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.
922

Supported Databases

SearchBlox databases for database collection indexing include:

  • MySQL
  • SQL Server
  • Oracle
  • PostgreSQL
  • Other Applications supported through Database Collection
Supported ConnectorsSupported ConnectorsSupported ConnectorsSupported ConnectorsSupported Connectors
Active DirectoryDynamics CRMJDBC-ODBC BridgeQuickBooksSharepoint
Adobe AnalyticsDynamics GPJIRAQuickBooks OnlineShopify
AlfrescoDynamics NAVJSONQuickBooks POSSlack
Amazon AthenaElasticsearch DriverKintoneRedisSmartsheet
Amazon DynamoDBEloquaLDAPRESTSnowflake
Amazon MarketplaceEmailLinkedInRSSSpark SQL
Amazon RedShiftExact OnlineMagentoSage 50 UKSplunk
Amazon S3ExcelMailChimpSage Business Cloud AccountingSQL Server
Apache HBaseExcel OnlineMariaDBSage IntacctSugarCRM
Apache HiveExcel ServicesMarketoSalesforceSurveyMonkey
Apache PhoenixFacebookMarkLogicSalesforce ChatterTeradata
Authorize.NetGmailMicrosoft ExchangeSalesforce Einstein AnalyticsTwilio
Azure Table StorageGoogle AdsMicrosoft PlannerSalesforce Marketing CloudTwitter
BaseCampGoogle Ads ManagerMicrosoft ProjectSalesforce PardotVeeva Vault
BoxGoogle AnalyticsMicrosoft TeamsSAP Business OneWasabi
BugzillaGoogle BigQueryMongoDBSAP Business One DIWordPress
CassandraGoogle Campaign ManagerMYOBSAP ByDesignXero
CosmosDBGoogle DirectoryMySQLSAP ConcurXero Workflow Max
CouchbaseGoogle DriveODataSAP ERPXML
CSVGoogle SearchOffice 365SAP HANAYouTube Analytics
DocuSignGoogle SpannerOracle DatabaseSAP Hybris Cloud for CustomerYouTube Data
DropBoxGoogle SpreadSheetsOracle Sales CloudSAP SuccessFactorsZendesk
Dynamics 365 Business CentralHPCCPinterestSAP Sybase ASEZoho CRM
Dynamics 365 Finance And OperationsHubSpotPostgreSQLSAP Sybase IQ
Dynamics 365 SalesInstagramQuandlServiceNow

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.
1048

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
FieldDescription
Database TypeDatabase 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 StringThe database URL string has to be specified for Supported DB Connectors
Host IP/nameIP address or name of the database host. (Default value is localhost).
Host Port No.Port number of the database.
Database NameName of the database in which the tables are created.
Database UsernameUsername of the database.
Database PasswordPassword of the database.
Fetch SizeSpecifies the maximum number of records that can be fetched from the database. Default value is 100.
IntervalDefines the time span covered for the polling period. Default value is 10.
Keyword-in-Context DisplayThe keyword-in-context returns search results with the description displayed from content areas where the search term occurs.
BoostingBoost search terms for the collection by setting a value greater than 1 (maximum value 9999).
StemmingWhen stemming is enabled, inflected words are reduced to root form. For example, "running", "runs", and "ran" are the inflected form of "run".
Spelling SuggestionsWhen 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.

465

Indexing and Other Operations

The following operations can be performed in Database collections.

ActionDescription
IndexStarts the indexer for the selected collection.
ClearClears the current index for the selected collection.
Scheduled ActivityFor 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
445

👍

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

692
  • 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