All Databases

In order to have a unified interface, Infragate uses SQL for all of the mentioned data sources despite some not being native-SQL engines. For example, in Elasticsearch or OpenSearch, the SQL API is used, which only allows SELECT statements.

The system currently supports integration with the following data sources:

AWS Athena

• Supported operations: Read/Write
• Official Documentation

Connection Parameters:

• Access Key ID – AWS Access Key ID.
• Access Key – AWS Secret Access Key.
• Region – AWS Region.
• Schema – Database schema name.
• S3 Staging Directory – S3 staging directory (e.g., s3://my-bucket/athena/).
• Catalog – Data catalog name. Default: AwsDataCatalog.
• Workgroup – Workgroup name. Default: primary.

Query Example:

SELECT user_id, SUM(requests), AVG(latency) FROM usage LIMIT 10

AWS DynamoDB

• Supported operations: Read / Write
• Based on PartiQL

Connection Parameters:

• Access Key ID – AWS Access Key ID.
• Access Key – AWS Secret Access Key.
• Region – AWS Region.

Query Example: Assuming your Partition Key is user_id.

SELECT user_id, item FROM my-tbl WHERE user_id = :user_id

AWS OpenSearch

• Supported operations: Limited Read
• Official Documentation

Connection Parameters:

• Auth Method – Authentication method. One of: AWS Access Keys, Basic Auth.
• Hostname – OpenSearch hostname.
• If AWS Access Keys then
  • Access Key ID – AWS Access Key ID.
  • Secret Access Key – AWS Secret Access Key.
  • Region – AWS Region.
• If Basic Auth then
  • Username – Username for basic authentication.
  • Password – Password for basic authentication.

Query Example:

SELECT author, name, page_count, SCORE()
FROM library
WHERE QUERY('_exists_:"author" AND page_count:>200 AND (name:/star.*/ OR name:duna~)');

AWS Redshift

• Supported operations: Read / Write
• Official Documentation

Connection Parameters:

• Username – Database username.
• Password – Database password.
• Hostname – Database hostname.
• Port – Database port. Default: 5439.
• Database – Database name.
• SSL Mode – SSL mode (disable, allow, prefer, require, verify-ca, verify-full). Default: disable.

Query Example:

SELECT DISTINCT(stv_tbl_perm.id) AS table_id,
       TRIM(pg_database.datname) AS db_name,
       TRIM(pg_namespace.nspname) AS schema_name,
       TRIM(pg_class.relname) AS table_name
FROM stv_tbl_perm
JOIN pg_class ON pg_class.oid = stv_tbl_perm.id
JOIN pg_namespace ON pg_namespace.oid = pg_class.relnamespace
JOIN pg_database ON pg_database.oid = stv_tbl_perm.db_id;

Apache Doris

• Supported operations: Read / Write
• Documentation page

Connection Parameters:

• Username – Database username.
• Password – Database password.
• Hostname – Database hostname.
• Port – Database port. Default: 9030.
• Database – Database name.

Query Example:

SELECT Name FROM student WHERE age IN (18, 20, 25)

Apache Drill

• Supported operations: Read only
• Official Documentation

Connection Parameters:

• Username – Database username.
• Password – Database password.
• Hostname – Database hostname.
• Port – Database port. Default: 8047.
• Storage Plugin – Storage plugin name. Default: dfs.
• Use SSL – Use SSL connection (true / false). Default: false.

Query Example:

SELECT * FROM cp.`employee.json` WHERE full_name LIKE :search_term LIMIT 20

Apache Druid

• Supported operations: Read only
• Official Documentation

Connection Parameters:

• Username – Database username.
• Password – Database password.
• Hostname – Database hostname.
• Port – Database port. Default: 8082.

Query Example:

SELECT * FROM "wikiticker-2015-09-12-sampled" WHERE page LIKE :search_term LIMIT 10

Apache Hive

• Supported operations: Read / Write
• Official Documentation

Connection Parameters:

• Username – Database username.
• Password – Database password.
• Hostname – Database hostname.
• Port – Database port. Default: 10000.
• Database – Database name.
• Authentication – Authentication method (NONE, LDAP, KERBEROS, CUSTOM). Default: NONE.

Query Example:

SELECT department, COUNT(*) AS employees FROM staff GROUP BY department

ClickHouse

• Supported operations: Read / Write
• Official Documentation

Connection Parameters:

• Username – Database username.
• Password – Database password.
• Hostname – Database hostname.
• Port – Database port. Default: 8123.
• Database – Database name.
• Secure – Use secure connection (true / false). Default: false.

Query Example:

SELECT user_id, COUNT() AS requests FROM logs WHERE status = 'OK' GROUP BY user_id ORDER BY requests DESC LIMIT 5

CockroachDB

• Supported operations: Read / Write
• Official Documentation

Connection Parameters:

• Username – Database username.
• Password – Database password.
• Hostname – Database hostname.
• Port – Database port. Default: 26257.
• Database – Database name.
• SSL Mode – SSL mode (disable, require, verify-ca, verify-full). Default: require.

Query Example:

INSERT INTO users (id, name, email) VALUES (uuid_generate_v4(), 'John Doe', 'john@example.com')

Databricks SQL

• Supported operations: Read / Write
• Official Documentation

Connection Parameters:

• Hostname
• HTTP Path
• Catalog
• Schema
• Access Token

Query Example:

SELECT `franchiseID`, `review_date`, `review` AS `review_count`
FROM `samples`.`bakehouse`.`media_customer_reviews`
WHERE franchiseID = :franchiseID

Elasticsearch

• Supported operations: Limited Read
• Official Documentation

Connection Parameters:

• Username – Elasticsearch username.
• Password – Elasticsearch password.
• Hostname – Elasticsearch hostname.
• Port – Elasticsearch port. Default: 9200.

Query Example:

SELECT * FROM library ORDER BY page_count DESC LIMIT 5

Google BigQuery

• Supported operations: Read / Write
• Official Documentation

Connection Parameters:

• Project – GCP Project ID.
• Dataset – BigQuery dataset name.
• Credentials – Base64-encoded GCP Service Account JSON.
• Location – BigQuery location (e.g., us-central1). Default: us-central1.

Query Example:

SELECT * FROM employees WHERE first_name LIKE :search_term

Google Sheets

• Supported operations: Read only
• Only works with public sheets

Query Example:

SELECT * FROM "https://docs.google.com/spreadsheets/d/1_rN3lm0R_bU3NemO0s9pbFkY5LQPcuy1pscv8ZXPtg8/"

MSSQL (Microsoft SQL Server)

• Supported operations: Read / Write
• Official Documentation

Connection Parameters:

• Username – Database username.
• Password – Database password.
• Hostname – Database hostname.
• Port – Database port. Default: 1433.
• Database – Database name.

Query Example:

SELECT TOP 10 name, created_at FROM users WHERE active = 1 ORDER BY created_at DESC

MySQL

• Supported operations: Read / Write
• Official Documentation

Connection Parameters:

• Username – Database username.
• Password – Database password.
• Hostname – Database hostname.
• Port – Database port. Default: 3306.
• Database – Database name.

Query Example:

SELECT id, name, email FROM users WHERE created_at > NOW() - INTERVAL 7 DAY

Oracle

• Supported operations: Read / Write
• Official Documentation

Connection Parameters:

• Username – Database username.
• Password – Database password.
• Hostname – Database hostname.
• Port – Database port. Default: 1521.
• Service Name – Oracle service name.

Query Example:

SELECT first_name, last_name, salary FROM employees WHERE department_id = :department_id FETCH FIRST 10 ROWS ONLY

PostgreSQL

• Supported operations: Read / Write
• Official Documentation

Connection Parameters:

• Username – Database username.
• Password – Database password.
• Hostname – Database hostname.
• Port – Database port. Default: 5432.
• Database – Database name.

Query Example:

SELECT id, username, email FROM users WHERE last_login > NOW() - INTERVAL '30 days'

Snowflake

• Supported operations: Read / Write
• Official Documentation

Connection Parameters:

• Username – Database username.
• Password – Database password.
• Account Identifier – Snowflake account identifier.

Query Example:

SELECT region, SUM(sales) AS total_sales FROM orders GROUP BY region ORDER BY total_sales DESC