Generic ClickHouse
Instructions for connecting to a ClickHouse data warehouse as a source
Step 1: Allow access
- Make a note of your Prequel static IP
- Create a rule in a security group or firewall settings to whitelist:
- incoming connections to your host and port (usually
9440
) from the static IP. - outgoing connections from ports
1024
to65535
to the static IP.
- incoming connections to your host and port (usually
Step 2: Create reader user
Create a database user to perform the reading of the source data.
- Open a connection to your ClickHouse database.
- Create a user for the data transfer by executing the following SQL command.
CREATE USER <username>@'%' IDENTIFIED BY '<some-password>';
- Grant user required privileges on the database.
GRANT SELECT ON <{database.table|database.*|*.*}> TO <username>@'%';
GRANT CREATE TEMPORARY TABLE, S3 on *.* TO <username>@'%';
Understanding theCREATE TEMPORARY TABLE, S3
permissionsThe
CREATE TEMPORARY TABLE
andS3
permissions are required to efficiently transfer data from ClickHouse. Under the hood, these permissions are used to stage data in a temporary table and export compressed data into object storage for transferring. By definition, the temporary table will not exist outside of the session.
Step 3: Setup staging bucket
ClickHouse sources require a staging bucket to efficiently transfer data. Configure your staging bucket using one of the following guides:
Step 4: Add source to Prequel
Use the cURL request to add the configured ClickHouse source and staging bucket.
Updated 4 days ago