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 Synapse	eBay Analytics	Google Directory	MongoDB	Sage 50 UK	Snapchat Ads	xBase
  Access	Azure Table Storage	EdgarOnline	Google Drive	MYOB	Sage Business Cloud Accounting	Snowflake	X-Cart
  Act CRM	BaseCamp	Elasticsearch	Google Search	NetSuite	Sage Intacct	Spark SQL	Xero
  Active Directory	BigCommerce	Email	Google Spanner	OData	Salesforce	Splunk	Xero Workflow Max
  Act-On	Bing	EnterpriseDB	Google Sheets	Odoo	Salesforce Chatter	CDATA SQL Server	XML
  Acumatica	Bing Ads	Epicor ERP	GraphQL	Office 365	Salesforce Pardot	SSAS	YouTube Analytics
  ADLS	Box	Evernote	GreenPlum	OneDrive	SAP BusinessObject DI	Streak	Zendesk
  Adobe Analytics	Btrieve	Exact Online	HDFS	OneNote	SAP Business One	Stripe	Zoho Books
  Airtable	Bugzilla	Excel	Highrise	Open Exchange Rates	SAP Business One DI	SugarCRM	Zoho CRM
  Alfresco	Bullhorn CRM	Excel Online	HPCC	Oracle Eloqua	SAP ByDesign	SuiteCRM	Zuora
  Amazon Athena	Cassandra	Excel Services	HubSpot	Oracle oci	SAP Concur	SurveyMonkey
  Amazon DynamoDB	CDS	Exchange	IBM Cloud Object Storage	Oracle Sales Cloud	SAP ERP	Sybase
  Amazon Marketplace	Cloudant	Facebook	IBM Cloud SQL Query	Parquet	SAP Fieldglass	Sybase IQ
  Amazon S3	CockroachDB	Facebook Ads	Instagram	PayPal	SAP Gateway	Tableau CRM
  Apache CouchDB	Confluence	FedEx	JDBC-ODBC Bridge	Pinterest	SAP HANA	Tally
  Apache HBase	CosmosDB	Financial Edge NXT	JIRA	CDATA PostgreSQL	SAP HANA XSA	TaxJar
  Apache Hive	Couchbase	FinancialForce	JIRA Service Desk	Presto	SAP Hybris C4C	Teradata
  Apache Impala	CSV	FreshBooks	JSON	Quandl	SAP SuccessFactors	Trello
  Apache Kafka	DocuSign	FreshDesk	Kintone	QuickBase	SAS DataSets	TSheets
  Apache Phoenix	DropBox	FTP	LDAP	QuickBooks	SAS Xpt	Twilio
  API	Dynamics 365 Business Central	Gmail	LinkedIn	QuickBooks Online	SendGrid	Twitter
  Asana	Dynamics 365 Finance And Operations	Google Ads	LinkedIn Ads	QuickBooks POS	ServiceNow	Twitter Ads
  Authorize.Net	Dynamics 365 Sales	Google Ads Manager	Magento	Raiser Edge NXT	SF Marketing Cloud	UPS
  Autify	Databricks	Google Analytics	MailChimp	Reckon	SFTP	USPS
  Avalara	DataRobot	Google BigQuery	MariaDB	Redis	Sharepoint	Veeva Vault
  Avro	DigitalOcean	Google Calender	Marketo	Redshift	ShipStation	Wasabi
  AWS Cost Explorer	Dynamics CRM	Google Cloud Storage	MarkLogic	REST	Shopify	Wave Financial
  Azure Data Catalog	Dynamics GP	Google CM	Microsoft Planner	RSS	SingleStore	WooCommerce
  Azure DevOps	Dynamics NAV	Google Contacts	Microsoft Project	Sage 200	Slack	WordPress
  Azure Resource Management	eBay	Google Data Catalog	Microsoft Teams	Sage 300	Smartsheet	Workday

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.

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