[GitHub] [doris] TangSiyang2001 commented on issue #18480: [Feature] run S3 load with insert stmt as its backend

["TangSiyang2001 (via GitHub)" <gi...@apache.org>]

TangSiyang2001 commented on issue #18480:
URL: https://github.com/apache/doris/issues/18480#issuecomment-1518671335

   ## statement syntax
   
   ```sql
   -- we can describe all outer loads in this way
   INSERT INTO tbl_name
   (
       data_desc
   )
   WITH label
   WITH method
   (
       info
   )
   PROPERTIES
   (
   	...
   )
   COMMENT "comments";
   ```
   
   
   
   ### S3 Load
   
   original
   
   ```sql
    LOAD LABEL example_db.exmpale_label_1
       (
           DATA INFILE("s3://your_bucket_name/your_file.txt")
           INTO TABLE load_test
           COLUMNS TERMINATED BY ","
       )
       WITH S3
       (
           "AWS_ENDPOINT" = "AWS_ENDPOINT",
           "AWS_ACCESS_KEY" = "AWS_ACCESS_KEY",
           "AWS_SECRET_KEY"="AWS_SECRET_KEY",
           "AWS_REGION" = "AWS_REGION"
       )
       PROPERTIES
       (
           "timeout" = "3600"
       );
   ```
   
   to be like
   
   ```sql
   INSERT INTO load_test
   (
       DATA INFILE("s3://your_bucket_name/your_file.txt")
       COLUMNS TERMINATED BY ","
   )
   WITH LABEL example_db.exmpale_label_1
   WITH S3
   (
       "AWS_ENDPOINT" = "AWS_ENDPOINT",
       "AWS_ACCESS_KEY" = "AWS_ACCESS_KEY",
       "AWS_SECRET_KEY"="AWS_SECRET_KEY",
       "AWS_REGION" = "AWS_REGION"
   )
   PROPERTIES
   (
       "file" = "s3://your_bucket_name/your_file.txt",
       "column_separator" = ",",
       "timeout" = "3600"
   );
   ```
   
   
   
   ### Broker Load
   
   original
   
   ```sql
   LOAD LABEL broker_load_2022_03_23
   (
       DATA INFILE("hdfs://192.168.20.123:8020/user/hive/warehouse/ods.db/ods_demo_detail/*/*")
       INTO TABLE doris_ods_test_detail
       COLUMNS TERMINATED BY ","
     	(k1, tmp_k2, tmp_k3)
       SET (
           k2 = tmp_k2 + 1,
           k3 = tmp_k3 + 1
       )
   )
   WITH BROKER "broker_name_1" 
   ( 
       "username" = "hdfs", 
       "password" = "" 
   )
   PROPERTIES
   (
       "timeout"="1200",
       "max_filter_ratio"="0.1"
   );
   ```
   
   to be like
   
   ```sql
   INSERT INTO doris_ods_test_detail
   (
       DATA INFILE("hdfs://192.168.20.123:8020/user/hive/warehouse/ods.db/ods_demo_detail/*/*")
       COLUMNS TERMINATED BY ","
     	(k1, tmp_k2, tmp_k3)
       SET (
           k2 = tmp_k2 + 1,
           k3 = tmp_k3 + 1
       )
   )
   WITH BROKER hdfs
   (
       "username"="hdfs_user",
       "password"="hdfs_password"
   )
   PROPERTIES
   (
       "timeout" = "3600",
       "max_filter_ratio" = "0.1"
   );
   ```
   
   **BUT HOW TO SUPPORT LOAD INTO SEVERAL TABLE IN ONE STAMENT?**
   
   LIKE
   
   ```SQL
   LOAD LABEL example_db.label2
   (
       DATA INFILE("hdfs://hdfs_host:hdfs_port/input/file-10*")
       INTO TABLE `my_table1`
       PARTITION (p1)
       COLUMNS TERMINATED BY ","
       (k1, tmp_k2, tmp_k3)
       SET (
           k2 = tmp_k2 + 1,
           k3 = tmp_k3 + 1
       )
       DATA INFILE("hdfs://hdfs_host:hdfs_port/input/file-20*")
       INTO TABLE `my_table2`
       COLUMNS TERMINATED BY ","
       (k1, k2, k3)
   )
   WITH BROKER hdfs
   (
       "username"="hdfs_user",
       "password"="hdfs_password"
   );
   ```
   
   May I do it with several group of desc in this case, like: 
   
   ```sql
   INSERT INTO my_table1, my_table2
   (
       -- corresponding to table1
       DATA INFILE("hdfs://hdfs_host:hdfs_port/input/file-10*")
       PARTITION (p1)
       COLUMNS TERMINATED BY ","
       (k1, tmp_k2, tmp_k3)
       SET (
           k2 = tmp_k2 + 1,
           k3 = tmp_k3 + 1
       )
   )
   (
       -- corresponding to table2
       DATA INFILE("hdfs://hdfs_host:hdfs_port/input/file-20*")
       COLUMNS TERMINATED BY ","
       (k1, k2, k3)
   )
   WITH BROKER hdfs
   (
       "username"="hdfs_user",
       "password"="hdfs_password"
   )
   PROPERTIES
   (
       "timeout" = "3600",
       "max_filter_ratio" = "0.1"
   );
   ```


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org