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. |
NOTE:
- While providing a
SQL Query
inDatabase Settings
page, make sure to provide the primary key asuid
else while reindexing/scheduled indexing duplicate documents gets added.- If you notice document missing while scheduled index/reindex, please increase the
SQL Settings-Fetch Size
, which you will find underSettings
page as shown in the following screenshots.
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.
Activity | Description |
---|---|
Enable Scheduler for Indexing | Once enabled, you can set the Start Date and Frequency |
Save | For each collection, indexing can be scheduled based on the above options. |
View all Collection Schedules | Redirects to the Schedules section, where all the Collection Schedules are listed. |
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 3 days ago