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.
1480
  • 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 ConnectorsSupported ConnectorsSupported ConnectorsSupported ConnectorsSupported ConnectorsSupported ConnectorsSupported ConnectorsSupported Connectors
AASAzure SynapseeBay AnalyticsGoogle DirectoryMongoDBSage 50 UKSnapchat AdsxBase
AccessAzure Table StorageEdgarOnlineGoogle DriveMYOBSage Business Cloud AccountingSnowflakeX-Cart
Act CRMBaseCampElasticsearchGoogle SearchNetSuiteSage IntacctSpark SQLXero
Active DirectoryBigCommerceEmailGoogle SpannerODataSalesforceSplunkXero Workflow Max
Act-OnBingEnterpriseDBGoogle SheetsOdooSalesforce ChatterCDATA SQL ServerXML
AcumaticaBing AdsEpicor ERPGraphQLOffice 365Salesforce PardotSSASYouTube Analytics
ADLSBoxEvernoteGreenPlumOneDriveSAP BusinessObject DIStreakZendesk
Adobe AnalyticsBtrieveExact OnlineHDFSOneNoteSAP Business OneStripeZoho Books
AirtableBugzillaExcelHighriseOpen Exchange RatesSAP Business One DISugarCRMZoho CRM
AlfrescoBullhorn CRMExcel OnlineHPCCOracle EloquaSAP ByDesignSuiteCRMZuora
Amazon AthenaCassandraExcel ServicesHubSpotOracle ociSAP ConcurSurveyMonkey
Amazon DynamoDBCDSExchangeIBM Cloud Object StorageOracle Sales CloudSAP ERPSybase
Amazon MarketplaceCloudantFacebookIBM Cloud SQL QueryParquetSAP FieldglassSybase IQ
Amazon S3CockroachDBFacebook AdsInstagramPayPalSAP GatewayTableau CRM
Apache CouchDBConfluenceFedExJDBC-ODBC BridgePinterestSAP HANATally
Apache HBaseCosmosDBFinancial Edge NXTJIRACDATA PostgreSQLSAP HANA XSATaxJar
Apache HiveCouchbaseFinancialForceJIRA Service DeskPrestoSAP Hybris C4CTeradata
Apache ImpalaCSVFreshBooksJSONQuandlSAP SuccessFactorsTrello
Apache PhoenixDocuSignFreshDeskKintoneQuickBaseSAS DataSetsTSheets
APIDropBoxFTPLDAPQuickBooksSAS XptTwilio
AsanaDynamics 365 Business CentralGmailLinkedInQuickBooks OnlineSendGridTwitter
Authorize.NetDynamics 365 Finance And OperationsGoogle AdsLinkedIn AdsQuickBooks POSServiceNowTwitter Ads
AutifyDynamics 365 SalesGoogle Ads ManagerMagentoRaiser Edge NXTSF Marketing CloudUPS
AvalaraDatabricksGoogle AnalyticsMailChimpReckonSFTPUSPS
AvroDataRobotGoogle BigQueryMariaDBRedisSharepointVeeva Vault
AWS Cost ExplorerDigitalOceanGoogle CalenderMarketoRedshiftShipStationWasabi
Azure Data CatalogDynamics CRMGoogle Cloud StorageMarkLogicRESTShopifyWave Financial
Azure DevOpsDynamics GPGoogle CMMicrosoft PlannerRSSSingleStoreWooCommerce
Azure Resource ManagementDynamics NAVGoogle ContactsMicrosoft ProjectSage 200SlackWordPress
eBayGoogle Data CatalogMicrosoft TeamsSage 300SmartsheetWorkday

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
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.
SQL QuerySQL Query to fetch the table for indexing.
example
Relevance - Remove DuplicatesAvoids the indexing of duplicate documents, i.e., documents which have the same exact content. The default is NO
Relevance - StemmingStemming 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 SuggestionsProvide spelling suggestions for the collection. The default is YES.
Keyword-in-Context DisplayThe keyword-in-context returns search results with the description displayed from content areas where the search term occurs.
SQL Settings - Fetch SizeSpecifies the maximum number of records that can be fetched from the database in every request. The default is 100.
Enable Detailed Log SettingsWhen 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 APIProvides 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.

1920 1642

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.

ActionDescription
ScheduleFor each collection, indexing can be scheduled based on the above options.
1674

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

692

If you want search results in the grid view, use SearchBlox's Database plugin. Learn more about Database Search