CLI: Importing Databases Using the ImportDatabase Tool in Cognitive Toolkit

Supported Databases

• Native support:

  • MS SQL Server

  • Oracle

  • Postgres

  • MySQL

• ODBC support: Virtually any data source configurable with 64-bit 'Windows ODBC Data Sources' applications (e.g., SAP HANA, SQL Server, Oracle, etc.)

Important considerations

• This process performs a straightforward import, capturing all new entries as they appear. ImportDatabase does not support delta operations.

  • Entries removed from the database will remain in the Shinydocs Search Engine.

  • Entries modified in the database will be updated in the Shinydocs Search Engine as long as the IDs are maintained in the database

  • A change in ID will result in a new entry in the Shinydocs Search Engine.

• ODBC support relies on existing Windows ODBC configurations. While we don’t set up ODBC connections, we seamlessly utilize pre-configured ones.

• The ImportDatabase tool is accessible exclusively via the CLI and is a specialized hidden tool.

• Permissioned search is not supported at this time.

Requirements for Using the ImportDatabase Tool

1. Database Configuration:

   • A valid connection string is required to establish the connection. Ensure you have the correct connection string format for your database type.

   • Ensure the database allows remote connections.

   • Ensure the database is configured for TCP/IP connections.

   • Mixed authentication should be supported (basic authentication with username and password).

2. SQL Query:

   • Prepare a database query (SQL query) to specify the data you want to import.

   • Include a unique identifiable field (e.g., ID) or a combination of fields that make each entry unique in the query.

   • Know the exact, case-sensitive field names that contain these unique IDs and supply them in the import command for the CLI.

Encrypting the Database Password

You can encrypt the connection string password for added security. Follow these steps:

1. Save the Encrypted Password:

   CognitiveToolkit.exe SaveValue
   

   1. Key name: connection-string-password

   2. Value: <your connection string password>

2. Replace the Password in the Connection String: When running a database import with ImportDatabase, replace the password in the connection string with {{password}}.

Example command

CognitiveToolkit.exe ImportDatabase --database-type mysql --key-fields "Code" --index-server-url http://localhost:9200 --index-name import_mysql_1 --connection-string "Server=localhost;Database=world;Uid=root;Pwd={{password}};" --sql-query-file-path "MYSQLQUERY.txt"

Examples

Commands

Oracle database

CognitiveToolkit.exe ImportDatabase --database-type oracle --key-fields "EMAIL" --index-server-url http://localhost:9200 --index-name import_ora_1 --connection-string "User Id=your_username; Password=your_password; Data Source=your_oracle_server:1521/your_service_name; Pooling=false; Statement Cache Size=1" --sql-query-file-path "your_oracle_query_file.txt"

PostgreSQL database

CognitiveToolkit.exe ImportDatabase --database-type postgres --key-fields "id" --index-server-url http://localhost:9200 --index-name import_pg_1 --connection-string "Host=your_host; Port=5432; Database=your_database; Username=your_username; Password=your_password;" --sql-query-file-path "your_postgres_query_file.txt"

SQL Server database

CognitiveToolkit.exe ImportDatabase --database-type sqlserver --connection-string "Server=your_server;Database=your_database;User Id=your_username;Password=your_password;TrustServerCertificate=True" --sql-query-file-path "your_sql_server_query_file.txt" --key-fields "employee_id" --index-server-url http://localhost:9200 --index-name import_sql_1

MySQL database

CognitiveToolkit.exe ImportDatabase --database-type mysql --key-fields "Code" --index-server-url http://localhost:9200 --index-name import_mysql_1 --connection-string "Server=your_server;Database=your_database;Uid=your_username;Pwd=your_password;" --sql-query-file-path "your_mysql_query_file.txt"

ODBC connection

CognitiveToolkit.exe ImportDatabase --database-type odbc --key-fields "employee_id" --index-server-url http://localhost:9200 --index-name import_odbc_1 --connection-string "Driver={SQL Server};server=your_server;database=your_database;trusted_connection=Yes;" --sql-query-file-path "your_sql_query_file.txt"

Connection strings

Oracle database

User Id=your_username; Password=your_password; Data Source=your_oracle_server:1521/your_service_name; Pooling=false; Statement Cache Size=1

PostgreSQL database

Host=your_host; Port=5432; Database=your_database; Username=your_username; Password=your_password;

SQL Server database

Server=your_server;Database=your_database;User Id=your_username;Password=your_password;TrustServerCertificate=True

MySQL database

Server=your_server;Database=your_database;Uid=your_username;Pwd=your_password;

ODBC connection

Driver={SQL Server};server=your_server;database=your_database;trusted_connection=Yes;

SQL Query

You would save this as a .txt file.

SELECT id, name, email FROM users;