Deploy a database connector

You can set up Google Cloud Search to discover and index data from your organization's databases by using the Google Cloud Search database connector.

• Google Workspace private key, which also contains the service account ID. To learn how to get a private key, go to Configure access to the Google Cloud Search REST API.
• Google Workspace data source ID. To learn how to get a data source ID, go to Add a data source to search.

Step 1. Download and build the database connector software

1. Clone the connector repository from GitHub.

   $ git clone https://github.com/google-cloudsearch/database-connector.git
   $ cd database-connector

2. Check out the desired version of the connector:
   

   $ git checkout tags/v1-0.0.3

3. Build the connector.

   $ mvn package

   To skip the tests when building the connector, use mvn package -DskipTests.
4. Copy the connector zip file to your local installation directory and unzip it:

   $ cp target/google-cloudsearch-database-connector-v1-0.0.3.zip installation-dir
   $ cd installation-dir
   $ unzip google-cloudsearch-database-connector-v1-0.0.3.zip
   $ cd google-cloudsearch-database-connector-v1-0.0.3

1. Create a text file and name it connector-config.properties (the default) or similar. Google recommends that you name configuration files with the .properties or .config extension and keep the file in the same directory as the connector. If you use a different name or path, you must specify the path when you run the connector.
2. Add parameters as key/value pairs to the file contents. The configuration file must specify the parameters for data source access, database access, a database full traversal SQL statement, a content field title, and column definitions. You can also configure other connector behavior with optional parameters. For example:

   # Required parameters for data source access
   api.sourceId=1234567890abcdef
   api.identitySourceId=0987654321lmnopq
   api.serviceAccountPrivateKeyFile=./PrivateKey.json
   #
   # Required parameters for database access
   db.url=jdbc:mysql://localhost:3306/mysql_test
   db.user=root
   db.password=passw0rd
   #
   # Required full traversal SQL statement parameter
   db.allRecordsSql=select customer_id, first_name, last_name, phone, change_timestamp from address_book
   #
   # Required parameters for column definitions and URL format
   db.allColumns=customer_id, first_name, last_name, phone, change_timestamp
   db.uniqueKeyColumns=customer_id
   url.columns=customer_id
   #
   # Required content field parameter
   contentTemplate.db.title=customer_id
   #
   # Optional parameters to set ACLs to "entire domain" access
   defaultAcl.mode=fallback
   defaultAcl.public=true
   #
   # Optional parameters for schedule traversals
   schedule.traversalIntervalSecs=36000
   schedule.performTraversalOnStart=true
   schedule.incrementalTraversalIntervalSecs=3600

   For detailed descriptions of the database-specific parameters, go to the Configuration parameters reference at the end of this article.

   To learn about the parameters that are common to all Cloud Search connectors, such as metadata configuration, datetime formats, and ACL options, go to Google-supplied connector parameters.

   If applicable, specify properties of the schema object in the traversal SQL query parameters. Usually you can add aliases to the SQL statement. For example, if you have a movie database and the data source schema contains a property definition named "ActorName", a SQL statement could have the form: SELECT …, last_name AS ActorName, … FROM … .

Step 3. Run the database connector

The following example assumes the required components are located in the local directory on a Linux system.

To run the connector from the command line, enter the following command:

java \
   -cp "google-cloudsearch-database-connector-v1-0.0.3.jar:mysql-connector-java-5.1.41-bin.jar" \
   com.google.enterprise.cloudsearch.database.DatabaseFullTraversalConnector \
   [-Dconfig=mysql.config]

Where:

• google-cloud-search-database-connector-v1-0.0.3.jar is the database connector .jar file
• mysql-connector-java-5.1.41-bin.jar is the JDBC driver being used to access the database
• mysql.config is a custom-named configuration file. To ensure the connector recognizes your configuration file, specify its path on the command line. Otherwise, the connector uses connector-config.properties in your local directory as the default filename.

The connector reports configuration errors as it detects them. Some errors are reported when the connector initializes, such as when a database column is defined as part of the record content (in db.allColumns), but the column isn't used in the traversal SQL query of the database (in db.allRecordsSql). Other errors are only detected and reported when the connector attempts to access the database for the first traversal, such as invalid SQL statement syntax.

The connector executes these traversals according to the schedules you define in traversal schedule parameters.

Traversal SQL query examples

• Basic full traversal query that reads every record of interest in an employee database for indexing:

  db.allRecordsSql = SELECT customer_id, first_name, last_name, employee_id, interesting_field \
      FROM employee

• Specify pagination by offset, and break up a full traversal into multiple queries.

  For SQL Server 2012 or Oracle 12c (standard SQL 2008 syntax):

  db.allRecordsSql = SELECT customer_id, first_name, last_name, employee_id, interesting_field \
      FROM employee \
      ORDER BY customer_id OFFSET ? ROWS FETCH FIRST 1000 ROWS ONLY
  db.allRecordsSql.pagination = offset

  or, for MySQL or Google Cloud SQL:

  db.allRecordsSql = SELECT customer_id, first_name, last_name, employee_id, interesting_field \
      FROM employee \
      ORDER BY customer_id LIMIT 1000 OFFSET ?
  db.allRecordsSql.pagination = offset

• Full traversal query that applies individual ACLs with aliases:

  db.allRecordsSql = SELECT customer_id, first_name, last_name,  employee_id, interesting_field, last_update_time, \
       permitted_readers AS readers_users, \
       denied_readers AS denied_users, \
       permitted_groups AS readers_groups, \
       denied_groups AS denied_groups \
       FROM employee

• Basic incremental traversal query:

  db.incrementalUpdateSql = SELECT customer_id, first_name, last_name, employee_id, interesting_field, last_update_time \
       FROM employee \
       WHERE last_update_time > ?

• Incremental traversal query that applies individual ACLs with aliases:

  db.incrementalUpdateSql = SELECT customer_id, first_name, last_name, employee_id, interesting_field, last_update_time, \
       permitted_readers AS readers_users, \
       denied_readers AS denied_users, \
       permitted_groups AS readers_groups, \
       denied_groups AS denied_groups \
       FROM employee \
       WHERE last_update_time > ?

• Incremental traversal query that uses the database timestamp rather than the current time:

  db.incrementalUpdateSql = SELECT customer_id, first_name, last_name, employee_id, interesting_field, \
       last_update_time AS timestamp_column \
       FROM employee \
       WHERE last_update_time > ?

Column definition parameters

The following parameters specify the columns that you use in the traversal statements and to uniquely identify each record.

db.allColumns = customer_id, first_name, last_name, phone, change_timestamp

db.uniqueKeyColumns = customer_id
# or
db.uniqueKeyColumns = last_name, first_name

URL column examples

To specify the columns used in traversal queries and the format of the view URL:

• To use a static URL not using any database record values:

  url.format = https://www.example.com

• To use a single column value that is the view URL:

  url.format = {0}
  url.columns = customer_id

• To use a single column value that is substituted into the view URL at position {0}:

  url.format = https://www.example.com/customer/id={0}
  url.columns = customer_id
  url.columnsToEscape = customer_id

• To use multiple column values to build the view URL (columns are order-dependent):

  url.format = {1}/customer={0}
  url.columns = customer_id, linked_url
  url.columnsToEscape = customer_id

Content fields

Use the content options to define which record values should be made part of the searchable content.