Import Data from MySQL

Create a table in ClickHouse based on the MySQL table structure. For the mapping between MySQL data types and ClickHouse types, refer to: Developer Guide

MySQL Table Creation:


CREATE TABLE testdb.mysql_test_table (
  id int NOT NULL,
  quarter tinyint unsigned DEFAULT NULL,
  month tinyint DEFAULT NULL,
  day_of_month smallint unsigned DEFAULT NULL,
  day_of_week smallint DEFAULT NULL,
  airline_id int DEFAULT NULL,
  carrier float DEFAULT NULL,
  origin double DEFAULT NULL,
  unique_carrier varchar(80) NOT NULL,
  flight_date date NOT NULL,
  tail_date datetime DEFAULT NULL,
  origin_airport_time timestamp,
  comment varchar(100)
) ENGINE=InnoDB

Create a corresponding table in ClickHouse:


--Create a local table
create table default.clickhouse_test_table ON CLUSTER ch_cluster (
  id Int32,
  quarter Nullable(UInt32),
  month Nullable(Int8),
  day_of_month Nullable(UInt16),
  day_of_week Nullable(Int16),
  airline_id Nullable(Int32),
  carrier Nullable(Float32),
  origin Nullable(Float64),
  unique_carrier String,
  flight_date Date,
  tail_date Nullable(Datetime),
  origin_airport_time Nullable(Datetime),
  comment Nullable(String)
) ENGINE = ReplicatedMergeTree(
    '/clickhouse/tables/clickhouse_test_table/{shard}',
    '{replica}',
    flight_date,
    (id, unique_carrier),
    8192);

--Create a distributed table
CREATE TABLE clickhouse_test_table_distributed ON CLUSTER ch_cluster
 AS clickhouse_test_table
ENGINE = Distributed(default, default, clickhouse_test_table, rand());

Insert Data


insert into <table_name> select * 
from mysql('<host>:<port>', '<db_name>','<table_name>', '<username>', '<password>')

Query Data


select count(*) from <table_name>