github.com/bdollma-te/migrate/v4@v4.17.0-clickv2/database/clickhouse/examples/migrations/003_create_database.up.sql (about) 1 CREATE DATABASE IF NOT EXISTS analytics; 2 3 CREATE TABLE IF NOT EXISTS analytics.driver_ratings( 4 rate UInt8, 5 userID Int64, 6 driverID String, 7 orderID String, 8 inserted_time DateTime DEFAULT now() 9 ) ENGINE = MergeTree 10 PARTITION BY driverID 11 ORDER BY (inserted_time); 12 13 CREATE TABLE analytics.driver_ratings_queue( 14 rate UInt8, 15 userID Int64, 16 driverID String, 17 orderID String 18 ) ENGINE = Kafka 19 SETTINGS kafka_broker_list = 'broker:9092', 20 kafka_topic_list = 'driver-ratings', 21 kafka_group_name = 'rating_readers', 22 kafka_format = 'Avro', 23 kafka_max_block_size = 1048576; 24 25 CREATE MATERIALIZED VIEW analytics.driver_ratings_queue_mv TO analytics.driver_ratings AS 26 SELECT rate, userID, driverID, orderID 27 FROM analytics.driver_ratings_queue; 28 29 CREATE TABLE IF NOT EXISTS analytics.user_ratings( 30 rate UInt8, 31 userID Int64, 32 driverID String, 33 orderID String, 34 inserted_time DateTime DEFAULT now() 35 ) ENGINE = MergeTree 36 PARTITION BY userID 37 ORDER BY (inserted_time); 38 39 CREATE TABLE analytics.user_ratings_queue( 40 rate UInt8, 41 userID Int64, 42 driverID String, 43 orderID String 44 ) ENGINE = Kafka 45 SETTINGS kafka_broker_list = 'broker:9092', 46 kafka_topic_list = 'user-ratings', 47 kafka_group_name = 'rating_readers', 48 kafka_format = 'JSON', 49 kafka_max_block_size = 1048576; 50 51 CREATE MATERIALIZED VIEW analytics.user_ratings_queue_mv TO analytics.user_ratings AS 52 SELECT rate, userID, driverID, orderID 53 FROM analytics.user_ratings_queue; 54 55 CREATE TABLE IF NOT EXISTS analytics.orders( 56 from_place String, 57 to_place String, 58 userID Int64, 59 driverID String, 60 orderID String, 61 inserted_time DateTime DEFAULT now() 62 ) ENGINE = MergeTree 63 PARTITION BY driverID 64 ORDER BY (inserted_time); 65 66 CREATE TABLE analytics.orders_queue( 67 from_place String, 68 to_place String, 69 userID Int64, 70 driverID String, 71 orderID String 72 ) ENGINE = Kafka 73 SETTINGS kafka_broker_list = 'broker:9092', 74 kafka_topic_list = 'orders', 75 kafka_group_name = 'order_readers', 76 kafka_format = 'Avro', 77 kafka_max_block_size = 1048576; 78 79 CREATE MATERIALIZED VIEW analytics.orders_queue_mv TO orders AS 80 SELECT from_place, to_place, userID, driverID, orderID 81 FROM analytics.orders_queue;