Load data into MySQL not into Minio

fhueske · fhueske · commit d302bfff6a82 · 2020-04-20T12:02:45.000+02:00
diff --git a/data-image/Dockerfile b/data-image/Dockerfile
@@ -43,14 +43,6 @@ RUN mkdir -p /opt/data; \
     mkdir -p /opt/data/minio; \
     mkdir -p /opt/data/kafka;
 
-# Copy Minio data files
-COPY ./data/*.tbl /opt/data/minio/
-
-# Link Kafka data files
-RUN ln -s /opt/data/minio/lineitem.tbl /opt/data/kafka/lineitem.tbl; \
-    ln -s /opt/data/minio/orders.tbl /opt/data/kafka/orders.tbl; \
-    ln -s /opt/data/minio/rates.tbl /opt/data/kafka/rates.tbl;
-
 # get mc and configure (minio client)
 RUN cd /opt; \
     wget https://dl.min.io/client/mc/release/linux-amd64/mc; \
diff --git a/data/README.md b/data/README.md
diff --git a/docker-compose.yml b/docker-compose.yml
@@ -26,6 +26,9 @@ services:
       - MC_HOST_minio=http://sql-demo:demo-sql@minio:9000
       - KAFKA_HOST=kafka:9092
       - SPEEDUP=10.0
+    volumes:
+      - ./data:/opt/data/kafka
+#      -./data:/opt/data/minio  # all files located in /opt/data/minio are copied into Minio
     depends_on:
       - minio
       - kafka
@@ -58,6 +61,8 @@ services:
     build: ./metastore-image
     expose:
       - "9083"
+    ports:
+      - "9083:9083"
   minio:
     image: minio/minio:latest
     entrypoint: sh
@@ -89,12 +94,13 @@ services:
       - /var/run/docker.sock:/var/run/docker.sock
   mysql:
     image: mysql:8.0.19
-    command: --default-authentication-plugin=mysql_native_password
+    command: --default-authentication-plugin=mysql_native_password --secure_file_priv=/data
     environment:
       MYSQL_USER: "sql-demo"
       MYSQL_PASSWORD: "demo-sql"
       MYSQL_DATABASE: "sql-demo"
       MYSQL_RANDOM_ROOT_PASSWORD: "yes"
     volumes:
       - ./mysql-init:/docker-entrypoint-initdb.d
+      - ./data:/data
 
diff --git a/flinksql-init/create_tables.sql b/flinksql-init/create_tables.sql
@@ -1,16 +1,5 @@
 USE CATALOG hcat;
 
-CREATE TABLE dev_region (
-  r_regionkey     INTEGER,
-  r_name          STRING,
-  r_comment       STRING
-) WITH (
-  'connector.type' = 'filesystem',
-  'connector.path' = 's3://sql-demo/region.tbl',
-  'format.type' = 'csv',
-  'format.field-delimiter' = '|'
-);
-
 CREATE TABLE prod_region (
   r_regionkey     INTEGER,
   r_name          STRING,
@@ -26,18 +15,6 @@ CREATE TABLE prod_region (
   'connector.lookup.cache.ttl' = '0s'
 );
 
-CREATE TABLE dev_nation (
-  n_nationkey     INTEGER,
-  n_name          STRING,
-  n_regionkey     INTEGER,
-  n_comment       STRING
-) WITH (
-  'connector.type' = 'filesystem',
-  'connector.path' = 's3://sql-demo/nation.tbl',
-  'format.type' = 'csv',
-  'format.field-delimiter' = '|'
-);
-
 CREATE TABLE prod_nation (
   n_nationkey     INTEGER,
   n_name          STRING,
@@ -54,22 +31,6 @@ CREATE TABLE prod_nation (
   'connector.lookup.cache.ttl' = '0s'
 );
 
-CREATE TABLE dev_customer (
-  c_custkey       INTEGER,
-  c_name          STRING,
-  c_address       STRING,
-  c_nationkey     INTEGER,
-  c_phone         STRING,
-  c_acctbal       DOUBLE,
-  c_mktsegment    STRING,
-  c_comment       STRING
-) WITH (
-  'connector.type' = 'filesystem',
-  'connector.path' = 's3://sql-demo/customer.tbl',
-  'format.type' = 'csv',
-  'format.field-delimiter' = '|'
-);
-
 CREATE TABLE prod_customer (
   c_custkey       INTEGER,
   c_name          STRING,
@@ -90,17 +51,6 @@ CREATE TABLE prod_customer (
   'connector.lookup.cache.ttl' = '0s'
 );
 
-CREATE TABLE dev_rates (
-  rs_timestamp    TIMESTAMP(3),
-  rs_symbol       STRING,
-  rs_rate         DOUBLE
-) WITH (
-  'connector.type' = 'filesystem',
-  'connector.path' = 's3://sql-demo/rates.tbl',
-  'format.type' = 'csv',
-  'format.field-delimiter' = '|'
-);
-
 CREATE TABLE prod_rates (
   rs_timestamp    TIMESTAMP(3),
   rs_symbol       STRING,
@@ -116,45 +66,4 @@ CREATE TABLE prod_rates (
   'connector.lookup.cache.ttl' = '0s'
 );
 
-CREATE TABLE dev_orders (
-  o_orderkey      INTEGER,
-  o_custkey       INTEGER,
-  o_orderstatus   STRING,
-  o_totalprice    DOUBLE,
-  o_currency      STRING,
-  o_ordertime     TIMESTAMP(3),
-  o_orderpriority STRING,
-  o_clerk         STRING, 
-  o_shippriority  INTEGER,
-  o_comment       STRING,
-  WATERMARK FOR o_ordertime AS o_ordertime - INTERVAL '5' MINUTE
-) WITH (
-  'connector.type' = 'filesystem',
-  'connector.path' = 's3://sql-demo/orders.tbl',
-  'format.type' = 'csv',
-  'format.field-delimiter' = '|'
-);
-
-CREATE TABLE dev_lineitem (
-  l_orderkey       INTEGER,
-  l_partkey        INTEGER,
-  l_suppkey        INTEGER,
-  l_linenumber     INTEGER,
-  l_quantity       DOUBLE,
-  l_extendedprice  DOUBLE,
-  l_discount       DOUBLE,
-  l_tax            DOUBLE,
-  l_currency       STRING,
-  l_returnflag     STRING,
-  l_linestatus     STRING,
-  l_ordertime      TIMESTAMP(3),
-  l_shipinstruct   STRING,
-  l_shipmode       STRING,
-  l_comment        STRING
-) WITH (
-  'connector.type' = 'filesystem',
-  'connector.path' = 's3://sql-demo/lineitem.tbl',
-  'format.type' = 'csv',
-  'format.field-delimiter' = '|'
-);
-
+QUIT;
diff --git a/mysql-init/create_tables.sql b/mysql-init/create_tables.sql
@@ -38,3 +38,8 @@ INSERT INTO PROD_RATES VALUES (TIMESTAMP '2020-04-19 10:01:52.154', 'CAD', 1.562
 INSERT INTO PROD_RATES VALUES (TIMESTAMP '2020-04-19 10:01:52.154', 'NOK', 10.9257);
 INSERT INTO PROD_RATES VALUES (TIMESTAMP '2020-04-19 10:01:52.154', 'RUB', 81.2930);
 INSERT INTO PROD_RATES VALUES (TIMESTAMP '2020-04-19 10:01:52.154', 'CNY', 8.0166);
+
+LOAD DATA INFILE "/data/region.tbl" INTO TABLE PROD_REGION COLUMNS TERMINATED BY '|' LINES TERMINATED BY '\n';
+LOAD DATA INFILE "/data/nation.tbl" INTO TABLE PROD_NATION COLUMNS TERMINATED BY '|' LINES TERMINATED BY '\n';
+LOAD DATA INFILE "/data/customer.tbl" INTO TABLE PROD_CUSTOMER COLUMNS TERMINATED BY '|' LINES TERMINATED BY '\n';
+LOAD DATA INFILE "/data/rates.tbl" INTO TABLE PROD_RATES COLUMNS TERMINATED BY '|' LINES TERMINATED BY '\n';
