Connect Roadway to Clickhouse

Data Warehouse Setup

Create Database Objects

**We use explicit CREATE statements with no additional clauses, whichprevents us from overwriting any existing objects of the same name. In the event you already have objects with these names, change the names in this script accordingly, but notify us of these changes.

Create the role

CREATE ROLE roadway_role;

Create the user with the specified password and default role

PLEASE REPLACE <some password> with some secure password

CREATE USER roadway_user
IDENTIFIED WITH plaintext_password BY '<some password>'
DEFAULT ROLE roadway_role
SETTINGS default_database = 'roadway';

Create the database

CREATE DATABASE roadway;

Grant privileges to roadway_role on roadway database

GRANT SELECT, CREATE TABLE, CREATE VIEW, DROP ON DATABASE roadway TO ROLE roadway_role;

Grant SELECT privilege on the entire system (all databases)

GRANT SELECT ON *.* TO ROLE roadway_role;

Grant CREATE VIEW privilege on the entire system (all databases)

GRANT CREATE VIEW ON *.* TO ROLE roadway_role;

Grant the role to the user

GRANT ROLE roadway_role TO USER roadway_user;