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, click Add Collection button. The Add Collection screen will now be displayed.
- Enter a unique name for your collection (for example, Databasecollection).
- Click on the DB Collection icon.
- Click Add to create the collection.
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 |
---|---|---|---|---|
Active Directory | Dynamics CRM | JDBC-ODBC Bridge | QuickBooks | Sharepoint |
Adobe Analytics | Dynamics GP | JIRA | QuickBooks Online | Shopify |
Alfresco | Dynamics NAV | JSON | QuickBooks POS | Slack |
Amazon Athena | Elasticsearch Driver | Kintone | Redis | Smartsheet |
Amazon DynamoDB | Eloqua | LDAP | REST | Snowflake |
Amazon Marketplace | RSS | Spark SQL | ||
Amazon RedShift | Exact Online | Magento | Sage 50 UK | Splunk |
Amazon S3 | Excel | MailChimp | Sage Business Cloud Accounting | SQL Server |
Apache HBase | Excel Online | MariaDB | Sage Intacct | SugarCRM |
Apache Hive | Excel Services | Marketo | Salesforce | SurveyMonkey |
Apache Phoenix | MarkLogic | Salesforce Chatter | Teradata | |
Authorize.Net | Gmail | Microsoft Exchange | Salesforce Einstein Analytics | Twilio |
Azure Table Storage | Google Ads | Microsoft Planner | Salesforce Marketing Cloud | |
BaseCamp | Google Ads Manager | Microsoft Project | Salesforce Pardot | Veeva Vault |
Box | Google Analytics | Microsoft Teams | SAP Business One | Wasabi |
Bugzilla | Google BigQuery | MongoDB | SAP Business One DI | WordPress |
Cassandra | Google Campaign Manager | MYOB | SAP ByDesign | Xero |
CosmosDB | Google Directory | MySQL | SAP Concur | Xero Workflow Max |
Couchbase | Google Drive | OData | SAP ERP | XML |
CSV | Google Search | Office 365 | SAP HANA | YouTube Analytics |
DocuSign | Google Spanner | Oracle Database | SAP Hybris Cloud for Customer | YouTube Data |
DropBox | Google SpreadSheets | Oracle Sales Cloud | SAP SuccessFactors | Zendesk |
Dynamics 365 Business Central | HPCC | SAP Sybase ASE | Zoho CRM | |
Dynamics 365 Finance And Operations | HubSpot | PostgreSQL | SAP Sybase IQ | |
Dynamics 365 Sales | Quandl | ServiceNow |
Database Collection Settings
- You can configure your database for indexing by using the applicable SQL query, and the database related settings in the Database collection settings.
- To access the settings for the collection, click on the collection name in the collections list.
Database Collection Queries
- To fetch data from the table or application, input the required query in the SQL field under the Queries tab.
- To find the applicable query format and prerequisites for your collection, read: SQL Query for DB Collection.
Database Settings
- Under the Settings sub-tab you will find settings for the database and tunable parameters for the search functionality.
- 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
- The mandatory settings for Database collection for External DB Connectors are
- Database Type
- Database URL String
- 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. |
Fetch Size | Specifies the maximum number of records that can be fetched from the database. Default value is 100. |
Interval | Defines the time span covered for the polling period. Default value is 10. |
Keyword-in-Context Display | The keyword-in-context returns search results with the description displayed from content areas where the search term occurs. |
Boosting | Boost search terms for the collection by setting a value greater than 1 (maximum value 9999). |
Stemming | When stemming is enabled, inflected words are reduced to root form. For example, "running", "runs", and "ran" are the inflected form of "run". |
Spelling Suggestions | When enabled, a spelling index is created at the end of the indexing process. |
After saving the query and settings, test the connection. The connection has to be successful for
indexing to take place.
Indexing and Other Operations
The following operations can be performed in Database collections.
Action | Description |
---|---|
Index | Starts the indexer for the selected collection. |
Clear | Clears the current index for the selected collection. |
Scheduled Activity | For each collection, any of the following scheduled indexer activity can be set: Index - Set the frequency and the start date/time for indexing a collection. Clear - Set the frequency and the start date/time for clearing a collection. |
- Indexer activity is controlled from the Index sub-tab in the collection. The current status of an indexer for a particular collection is indicated.
- Indexing operation starts the indexer for the database collection.
- On reindexing that is, clicking on index again after the initial index operation, all crawled documents will be reindexed. If documents have been deleted from the database since the first index operation, they will be deleted from the index. New documents will also be indexed.
- Also, indexing is controlled from the Index sub-tab for a collection or through API. The current status of a collection is always indicated on the Collection Dashboard and the Index page.
- Index operation can also be initiated from the Collection Dashboard.
- Scheduling can be performed only from the Index sub-tab.
Schedule Frequency
Schedule Frequency supported in SearchBlox is as follows:
- Once
- Every Minute
- Hourly
- Daily
- Every 48 Hours
- Every 96 Hours
- Weekly
- Monthly
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.
- Do not schedule the same time for index and clear operations.
- 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: http://localhost:8080/searchblox/plugin/index.html.
- After clicking the search results, the data will appear in a grid format.
- To add customized facets, go to the index.html page. The facet can be any value in the table, which can be used to filter the results.
- Results can also be viewed in JSON format as shown here, by clicking the database search results in a regular search: http://localhost:8080/searchblox/search.jsp.
[{"uid":"1","lname":"Jones","university":"VCU","keywords":"joe","no":"1","content":"[1, Smith, Jones, 1, Georgetown, Biotech, D05, VCU]","title":"Smith","deptid":"D05","address":"1, Georgetown","dept":"Biotech","fname":"Smith"}]
If you want search results in the grid view, use SearchBlox's Database plugin. Learn more about Database Search
Updated about 4 years ago