Named collections

Named collections store key-value pairs, allowing you to configure integrations with external data sources. Named collections allow you to hide credentials from your queries to remote databases.

Create a named collection

To create a named collection, a user with admin privileges should send the following query:

CREATE NAMED COLLECTION your_collection_name AS
key1_name = 'value 1',
url = 'https://your_endpoint_url';

Now you can access your data without exposing your credentials in a query:

SELECT *
FROM s3(
   <collection_name>, 
   filename = 'Path_to_file', 
   format = 'source_format', 
   structure = 'data_structure'
)
LIMIT 2;

You can specify multiple keys per collection.

Query examples

Below you can find query examples to create named collections tailored for accessing various databases:

CREATE NAMED COLLECTION s3_data AS
access_key_id = '<access-key-id-value>',
secret_access_key = '<secret-access-key-value>',
url = 'https://s3.us-east-1.amazonaws.com/yourbucket/mydata/'

CREATE NAMED COLLECTION mysql_data AS
user = 'mysql_user',
password = 'mysql_pass',
database = 'test',

CREATE NAMED COLLECTION postgresql_data AS
user = 'pg_user',
password = 'pg_pass',
database = 'pg_database',
schema = 'pg_database_schema',

CREATE NAMED COLLECTION clickhouse_data AS
host = 'remote_host',
port = 8443,
database = 'ch_database_name',
user = 'ch_username',
password = 'ch_password',
secure = 1

Manage a named collection

You can add, change and remove the keys in a named collection.

To add a new key or to change the value of an existing one, send the following query:

ALTER NAMED COLLECTION <collection_name> SET 
<New_key_name>='new_key_value', 
<existing_key_name>='updated_existing_key_value'

To delete a key from the collection, send the following query:

ALTER NAMED COLLECTION <collection_name> DELETE <key_name>

Delete a named collection

To delete a named collection, a user with admin privileges should run the following query:

DROP NAMED COLLECTION <collection_name>