Database Collection
SearchBlox allows you to easily index records from the database table, and results are customizable with Faceted Search.
Creating a Database Collection
You can Create a Database Collection with the following steps:
- After logging in to the Admin Console, select the Collections tab and click on Create a New Collection or "+" icon.
- Choose Database Collection as Collection Type.
- Enter a unique name for your collection (for example, Databasecollection).
- Choose Private/Public Collection Access and Collection Encryption as per the requirements.
- Choose the language of the content (if the language is other than English).
- Click Save to create the collection.
- Once the DB collection is created you will be taken to the Database Settings tab.
Supported Databases
SearchBlox databases for database collection indexing include:
- MySQL
- SQL Server
- Oracle
- PostgreSQL
- Other Applications supported through Database Collection
Supported Connectors | Supported Connectors | Supported Connectors | Supported Connectors | Supported Connectors | Supported Connectors | Supported Connectors | Supported Connectors |
---|---|---|---|---|---|---|---|
AAS | Azure DevOps | HCL Domino | Odoo | Sage 300 | Snapchat Ads | Zendesk | |
Access | Azure Synapse | EnterpriseDB | HDFS | Office 365 | Sage 50 UK | Snowflake | Zoho Books |
Act CRM | Azure Table Storage | Epicor ERP | Highrise | OneDrive | Sage Business Cloud Accounting | Spark SQL | Zoho Creator |
Active Directory | BaseCamp | Exact Online | HubSpot | OneNote | Sage Intacct | Splunk | Zoho CRM |
ActiveCampaign | BigCommerce | Excel | IBM Cloud Object Storage | Oracle Cloud Financials(ERP) | Salesforce | SSAS | Zoho Inventory |
Act-On | Bing | Excel Online | IBM Cloud SQL Query | Oracle Eloqua | Salesforce Pardot | Streak | Zoho Projects |
Acumatica | Bing Ads | Excel Services | IBM DB2 | Oracle HCM Cloud | Salesloft | Stripe | Zuora |
ADLS | Box | Exchange | IBM Informix | Oracle oci | SAP BusinessObject DI | SugarCRM | |
Adobe Analytics | Bugzilla | Oracle Sales Cloud | SAP Business One | SuiteCRM | |||
Adobe Commerce | Bullhorn CRM | Facebook Ads | JDBC-ODBC Bridge | Oracle SCM | SAP ByDesign | SurveyMonkey | |
ADP | Cassandra | FreshBooks | JIRA | Oracle Service Cloud | SAP Concur | Sybase | |
Airtable | Certinia | FreshDesk | JIRA Service Desk | Outreach.io | SAP ERP | Sybase IQ | |
AlloyDB | CDS | FTP | JSON | Parquet | SAP Fieldglass | Tableau CRM | |
Amazon Athena | Cloudant | Gmail | Kintone | Paylocity | SAP Gateway | Tally | |
Amazon DynamoDB | CockroachDB | Google Ads | LDAP | PayPal | SAP HANA | TaxJar | |
Amazon Marketplace | Confluence | Google Ads Manager | SAP HANA XSA | Teradata | |||
Amazon S3 | CosmosDB | Google Analytics | LinkedIn Ads | Pipedrive | SAP Hybris C4C | Trello | |
Apache CouchDB | Couchbase | Google BigQuery | MailChimp | Power BI XMLA | SAP SuccessFactors | TSheets | |
Apache HBase | CSV | Google Calender | MariaDB | Presto | SAS DataSets | Twilio | |
Apache Hive | DocuSign | Google Cloud Storage | Marketo | QuickBase | SAS Xpt | Twitter Ads | |
Apache Impala | DropBox | Google CM | MarkLogic | QuickBooks | SendGrid | Veeva Vault | |
Apache Phoenix | Dynamics 365 Business Central | Google Contacts | Microsoft Planner | QuickBooks Online | ServiceNow | Wave Financial | |
API | Databricks | Google Data Catalog | Microsoft Project | QuickBooks POS | SF Marketing Cloud | WooCommerce | |
Asana | Dynamics 365 | Google Directory | Microsoft Teams | Raiser Edge NXT | SFTP | WordPress | |
Authorize.Net | Dynamics CRM | Google Drive | Monday.com | Reckon | Sharepoint | Workday | |
Autify | Dynamics GP | Google Search | MongoDB | Redis | ShipStation | xBase | |
Avalara | Dynamics NAV | Google Spanner | MYOB | Redshift | Shopify | Xero | |
Avro | eBay | Google Sheets | Neo4j | REST | SingleStore | Xero Workflow Max | |
Azure Active Directory | eBay Analytics | GraphQL | NetSuite | RSS | Slack | XML | |
Azure Data Catalog | Elasticsearch | GreenPlum | OData | Sage 200 | Smartsheet | YouTube Analytics |
Database Settings
- Database setting values must be set explicitly for Database collections.
- The mandatory settings for Database collection for databases like MySQL, Oracle, etc., are
- Database Type
- Host IP/Name
- Host Port No
- Database Name
- Database Username
- Database Password
- SQL Query
- The mandatory settings for Database collection for External DB Connectors are
- Database Type
- Database URL String
- SQL Query
- SearchBlox comes pre-configured with parameters when a new collection is created, but you can also select your preferred setting values for database collections.
- The following table has the list of settings available in Database collection
Field | Description |
---|---|
Database Type | Database to be indexed. SearchBlox supports MySQL, SQL Server, Oracle, PostgreSQL, and other drivers. The list of other drivers supported by SearchBlox is provided here: Supported DB Connectors |
Database URL String | The database URL string has to be specified for Supported DB Connectors |
Host IP/name | IP address or name of the database host. (Default value is localhost). |
Host Port No. | Port number of the database. |
Database Name | Name of the database in which the tables are created. |
Database Username | Username of the database. |
Database Password | Password of the database. |
SQL Query | SQL Query to fetch the table for indexing. example |
Relevance - Remove Duplicates | Avoids the indexing of duplicate documents, i.e., documents which have the same exact content. The default is NO |
Relevance - Stemming | Stemming considers the inflected words of the root form within the search page. For example, "running", "runs", and "ran" are all inflected forms of run. The default is YES. |
Relevance - Spelling Suggestions | Provide spelling suggestions for the collection. The default is YES. |
Keyword-in-Context Display | The keyword-in-context returns search results with the description displayed from content areas where the search term occurs. |
SQL Settings - Fetch Size | Specifies the maximum number of records that can be fetched from the database in every request. The default is 100. |
Enable Detailed Log Settings | When debug mode is enabled, indexing activity gets logged in detail within the index.log. Log details include: Indexing status of each URL along with timestamp, URL indexing status along with timestamp, status code and time taken for indexing. By default this is set to NO |
Enable Content API | Provides the ability to crawl the document content with special characters included. |
After saving the Database settings, test the connection. The connection has to be successful for
indexing to take place.
Schedule and Index
Sets the frequency and the start date/time for indexing a collection. Schedule Frequency supported in SearchBlox is as follows:
- Once
- Hourly
- Daily
- Every 48 Hours
- Every 96 Hours
- Weekly
- Monthly
The following operations can be performed in Database collections.
Action | Description |
---|---|
Schedule | For each collection, indexing can be scheduled based on the above options. |
Data Fields Tab
Using Data Fields tab we can create custom fields for search and we can see the Default Data Fields with non-encrypted collection. SearchBlox supports 4 types of Data Fields as listed below:
Keyword
Number
Date
Text
- Once the Data fields are configured, collection must be cleared and re-indexed to take effect.
To know more about Data Fields please refer to Data Fields Tab
Best Practices
- Please check the type of application/database that is to be indexed and provide the database settings accordingly.
- If you do not have any unique id to map in SQL SELECT query, create a view with a unique id and use the same in the SELECT query to fetch the data.
- If you have multiple collections, always schedule the activity to prevent more than 2-3 collections indexing at the same time.
Viewing Search Results
- View the search results by searching for the records here: https://localhost:8443/search/index.html.
- After clicking the search results, the data will appear in a grid format.
If you want search results in the grid view, use SearchBlox's Database plugin. Learn more about Database Search
Updated 25 days ago