BROKER LOAD

description

  1. Broker load 通过随 Doris 集群一同部署的 broker 进行，访问对应数据源的数据，进行数据导入。
  2. 可以通过 show broker 命令查看已经部署的 broker。
  3. 目前支持以下4种数据源：
  5. 1. Baidu HDFS：百度内部的 hdfs，仅限于百度内部使用。
  6. 2. Baidu AFS：百度内部的 afs，仅限于百度内部使用。
  7. 3. Baidu Object Storage(BOS)：百度对象存储。仅限百度内部用户、公有云用户或其他可以访问 BOS 的用户使用。
  8. 4. Apache HDFS：社区版本 hdfs。
  9. 5. Amazon S3：Amazon对象存储。

语法：

  1. LOAD LABEL load_label
  2. (
  3. data_desc1[, data_desc2, ...]
  4. )
  5. WITH BROKER broker_name
  6. [broker_properties]
  7. [opt_properties];
  9. 1. load_label
  11.     当前导入批次的标签。在一个 database 内唯一。
  12.     语法：
  13.     [database_name.]your_label
  15. 2. data_desc
  17.     用于描述一批导入数据。
  18.     语法：
  19.         DATA INFILE
  20.         (
  21.         "file_path1"[, file_path2, ...]
  22.         )
  23.         [NEGATIVE]
  24.         INTO TABLE `table_name`
  25.         [PARTITION (p1, p2)]
  26.         [COLUMNS TERMINATED BY "column_separator"]
  27.         [FORMAT AS "file_type"]
  28.         [(column_list)]
  29.         [SET (k1 = func(k2))]
  30.         [WHERE predicate]    
  32.     说明：
  33.         file_path: 
  35.         文件路径，可以指定到一个文件，也可以用 * 通配符指定某个目录下的所有文件。通配符必须匹配到文件，而不能是目录。
  37.         PARTITION:
  39.         如果指定此参数，则只会导入指定的分区，导入分区以外的数据会被过滤掉。
  40.         如果不指定，默认导入table的所有分区。
  42.         NEGATIVE：
  43.         如果指定此参数，则相当于导入一批“负”数据。用于抵消之前导入的同一批数据。
  44.         该参数仅适用于存在 value 列，并且 value 列的聚合类型仅为 SUM 的情况。
  46.         column_separator：
  48.         用于指定导入文件中的列分隔符。默认为 \t
  49.         如果是不可见字符，则需要加\\x作为前缀，使用十六进制来表示分隔符。
  50.         如hive文件的分隔符\x01，指定为"\\x01"
  52.         file_type：
  54.         用于指定导入文件的类型，例如：parquet、orc、csv。默认值通过文件后缀名判断。 
  56.         column_list：
  58.         用于指定导入文件中的列和 table 中的列的对应关系。
  59.         当需要跳过导入文件中的某一列时，将该列指定为 table 中不存在的列名即可。
  60.         语法：
  61.         (col_name1, col_name2, ...)
  63.         SET:
  65.         如果指定此参数，可以将源文件某一列按照函数进行转化，然后将转化后的结果导入到table中。语法为 `column_name` = expression。举几个例子帮助理解。
  66.         例1: 表中有3个列“c1, c2, c3", 源文件中前两列依次对应(c1,c2)，后两列之和对应c3；那么需要指定 columns (c1,c2,tmp_c3,tmp_c4) SET (c3=tmp_c3+tmp_c4); 
  67.         例2: 表中有3个列“year, month, day"三个列，源文件中只有一个时间列，为”2018-06-01 01:02:03“格式。
  68.         那么可以指定 columns(tmp_time) set (year = year(tmp_time), month=month(tmp_time), day=day(tmp_time)) 完成导入。
  70.         WHERE:
  72.         对做完 transform 的数据进行过滤，符合 where 条件的数据才能被导入。WHERE 语句中只可引用表中列名。
  73. 3. broker_name
  75.     所使用的 broker 名称，可以通过 show broker 命令查看。
  77. 4. broker_properties
  79.     用于提供通过 broker 访问数据源的信息。不同的 broker，以及不同的访问方式，需要提供的信息不同。
  81.     1. Baidu HDFS/AFS
  83.         访问百度内部的 hdfs/afs 目前仅支持简单认证，需提供：
  84.         username：hdfs 用户名
  85.         password：hdfs 密码
  87.     2. BOS
  89.         需提供：
  90.         bos_endpoint：BOS 的endpoint
  91.         bos_accesskey：公有云用户的 accesskey
  92.         bos_secret_accesskey：公有云用户的 secret_accesskey
  94.     3. Apache HDFS
  96.         社区版本的 hdfs，支持简单认证、kerberos 认证。以及支持 HA 配置。
  97.         简单认证：
  98.         hadoop.security.authentication = simple (默认)
  99.         username：hdfs 用户名
  100.         password：hdfs 密码
  102.         kerberos 认证：
  103.         hadoop.security.authentication = kerberos
  104.         kerberos_principal：指定 kerberos 的 principal
  105.         kerberos_keytab：指定 kerberos 的 keytab 文件路径。该文件必须为 broker 进程所在服务器上的文件。
  106.         kerberos_keytab_content：指定 kerberos 中 keytab 文件内容经过 base64 编码之后的内容。这个跟 kerberos_keytab 配置二选一就可以。
  108.         namenode HA：
  109.         通过配置 namenode HA，可以在 namenode 切换时，自动识别到新的 namenode
  110.         dfs.nameservices: 指定 hdfs 服务的名字，自定义，如："dfs.nameservices" = "my_ha"
  111.         dfs.ha.namenodes.xxx：自定义 namenode 的名字,多个名字以逗号分隔。其中 xxx 为 dfs.nameservices 中自定义的名字，如 "dfs.ha.namenodes.my_ha" = "my_nn"
  112.         dfs.namenode.rpc-address.xxx.nn：指定 namenode 的rpc地址信息。其中 nn 表示 dfs.ha.namenodes.xxx 中配置的 namenode 的名字，如："dfs.namenode.rpc-address.my_ha.my_nn" = "host:port"
  113.         dfs.client.failover.proxy.provider：指定 client 连接 namenode 的 provider，默认为：org.apache.hadoop.hdfs.server.namenode.ha.ConfiguredFailoverProxyProvider
  115.     4. Amazon S3
  117.         需提供：
  118.         fs.s3a.access.key：AmazonS3的access key
  119.         fs.s3a.secret.key：AmazonS3的secret key
  120.         fs.s3a.endpoint：AmazonS3的endpoint 
  122. 4. opt_properties
  124.     用于指定一些特殊参数。
  125.     语法：
  126.     [PROPERTIES ("key"="value", ...)]
  128.     可以指定如下参数：
  129.     timeout：         指定导入操作的超时时间。默认超时为4小时。单位秒。
  130.     max_filter_ratio：最大容忍可过滤（数据不规范等原因）的数据比例。默认零容忍。
  131.     exec_mem_limit：  导入内存限制。默认为 2GB。单位为字节。
  132.     strict mode：     是否对数据进行严格限制。默认为 false。
  133.     timezone:         指定某些受时区影响的函数的时区，如 strftime/alignment_timestamp/from_unixtime 等等，具体请查阅 [时区] 文档。如果不指定，则使用 "Asia/Shanghai" 时区。
  135. 5. 导入数据格式样例
  137.     整型类（TINYINT/SMALLINT/INT/BIGINT/LARGEINT）：1, 1000, 1234
  138.     浮点类（FLOAT/DOUBLE/DECIMAL）：1.1, 0.23, .356
  139.     日期类（DATE/DATETIME）：2017-10-03, 2017-06-13 12:34:03。
  140.     （注：如果是其他日期格式，可以在导入命令中，使用 strftime 或者 time_format 函数进行转换）
  141.     字符串类（CHAR/VARCHAR）："I am a student", "a"
  142.     NULL值：\N

example

  1. 1. 从 HDFS 导入一批数据，指定超时时间和过滤比例。使用铭文 my_hdfs_broker 的 broker。简单认证。
  3.     LOAD LABEL example_db.label1
  4.     (
  5.     DATA INFILE("hdfs://hdfs_host:hdfs_port/user/palo/data/input/file")
  6.     INTO TABLE `my_table`
  7.     )
  8.     WITH BROKER my_hdfs_broker
  9.     (
  10.     "username" = "hdfs_user",
  11.     "password" = "hdfs_passwd"
  12.     )
  13.     PROPERTIES
  14.     (
  15.     "timeout" = "3600",
  16.     "max_filter_ratio" = "0.1"
  17.     );
  19.     其中 hdfs_host 为 namenode 的 host，hdfs_port 为 fs.defaultFS 端口（默认9000）
  21. 2. 从 AFS 一批数据，包含多个文件。导入不同的 table，指定分隔符，指定列对应关系。
  23.     LOAD LABEL example_db.label2
  24.     (
  25.     DATA INFILE("afs://afs_host:hdfs_port/user/palo/data/input/file1")
  26.     INTO TABLE `my_table_1`
  27.     COLUMNS TERMINATED BY ","
  28.     (k1, k3, k2, v1, v2),
  29.     DATA INFILE("afs://afs_host:hdfs_port/user/palo/data/input/file2")
  30.     INTO TABLE `my_table_2`
  31.     COLUMNS TERMINATED BY "\t"
  32.     (k1, k2, k3, v2, v1)
  33.     )
  34.     WITH BROKER my_afs_broker
  35.     (
  36.     "username" = "afs_user",
  37.     "password" = "afs_passwd"
  38.     )
  39.     PROPERTIES
  40.     (
  41.     "timeout" = "3600",
  42.     "max_filter_ratio" = "0.1"
  43.     );
  46. 3. 从 HDFS 导入一批数据，指定hive的默认分隔符\x01，并使用通配符*指定目录下的所有文件。
  47.    使用简单认证，同时配置 namenode HA
  49.     LOAD LABEL example_db.label3
  50.     (
  51.     DATA INFILE("hdfs://hdfs_host:hdfs_port/user/palo/data/input/*")
  52.     INTO TABLE `my_table`
  53.     COLUMNS TERMINATED BY "\\x01"
  54.     )
  55.     WITH BROKER my_hdfs_broker
  56.     (
  57.     "username" = "hdfs_user",
  58.     "password" = "hdfs_passwd",
  59.     "dfs.nameservices" = "my_ha",
  60.     "dfs.ha.namenodes.my_ha" = "my_namenode1, my_namenode2",
  61.     "dfs.namenode.rpc-address.my_ha.my_namenode1" = "nn1_host:rpc_port",
  62.     "dfs.namenode.rpc-address.my_ha.my_namenode2" = "nn2_host:rpc_port",
  63.     "dfs.client.failover.proxy.provider" = "org.apache.hadoop.hdfs.server.namenode.ha.ConfiguredFailoverProxyProvider"
  64.     )
  66. 4. 从 HDFS 导入一批“负”数据。同时使用 kerberos 认证方式。提供 keytab 文件路径。
  68.     LOAD LABEL example_db.label4
  69.     (
  70.     DATA INFILE("hdfs://hdfs_host:hdfs_port/user/palo/data/input/old_file)
  71.     NEGATIVE
  72.     INTO TABLE `my_table`
  73.     COLUMNS TERMINATED BY "\t"
  74.     )
  75.     WITH BROKER my_hdfs_broker
  76.     (
  77.     "hadoop.security.authentication" = "kerberos",
  78.     "kerberos_principal"="doris@YOUR.COM",
  79.     "kerberos_keytab"="/home/palo/palo.keytab"
  80.     )
  82. 5. 从 HDFS 导入一批数据，指定分区。同时使用 kerberos 认证方式。提供 base64 编码后的 keytab 文件内容。
  84.     LOAD LABEL example_db.label5
  85.     (
  86.     DATA INFILE("hdfs://hdfs_host:hdfs_port/user/palo/data/input/file")
  87.     INTO TABLE `my_table`
  88.     PARTITION (p1, p2)
  89.     COLUMNS TERMINATED BY ","
  90.     (k1, k3, k2, v1, v2)
  91.     )
  92.     WITH BROKER my_hdfs_broker
  93.     (
  94.     "hadoop.security.authentication"="kerberos",
  95.     "kerberos_principal"="doris@YOUR.COM",
  96.     "kerberos_keytab_content"="BQIAAABEAAEACUJBSURVLkNPTQAEcGFsbw"
  97.     )
  99. 6. 从 BOS 导入一批数据，指定分区, 并对导入文件的列做一些转化，如下：
  100.    表结构为：
  101.     k1 varchar(20)
  102.     k2 int
  104.     假设数据文件只有一行数据：
  106.     Adele,1,1
  108.     数据文件中各列，对应导入语句中指定的各列：
  109.     k1,tmp_k2,tmp_k3
  111.     转换如下：
  113.     1) k1: 不变换
  114.     2) k2：是 tmp_k2 和 tmp_k3 数据之和
  116.     LOAD LABEL example_db.label6
  117.     (
  118.     DATA INFILE("bos://my_bucket/input/file")
  119.     INTO TABLE `my_table`
  120.     PARTITION (p1, p2)
  121.     COLUMNS TERMINATED BY ","
  122.     (k1, tmp_k2, tmp_k3)
  123.     SET (
  124.       k2 = tmp_k2 + tmp_k3
  125.     )
  126.     )
  127.     WITH BROKER my_bos_broker
  128.     (
  129.     "bos_endpoint" = "http://bj.bcebos.com",
  130.     "bos_accesskey" = "xxxxxxxxxxxxxxxxxxxxxxxxxx",
  131.     "bos_secret_accesskey"="yyyyyyyyyyyyyyyyyyyy"
  132.     )
  134. 7. 导入数据到含有HLL列的表，可以是表中的列或者数据里面的列
  136.     如果表中有三列分别是（id,v1,v2,v3）。其中v1和v2列是hll列。导入的源文件有3列。则（column_list）中声明第一列为id，第二三列为一个临时命名的k1,k2。
  137.     在SET中必须给表中的hll列特殊声明 hll_hash。表中的v1列等于原始数据中的hll_hash(k1)列, 表中的v3列在原始数据中并没有对应的值，使用empty_hll补充默认值。
  138.     LOAD LABEL example_db.label7
  139.     (
  140.     DATA INFILE("hdfs://hdfs_host:hdfs_port/user/palo/data/input/file")
  141.     INTO TABLE `my_table`
  142.     PARTITION (p1, p2)
  143.     COLUMNS TERMINATED BY ","
  144.     (id, k1, k2)
  145.     SET (
  146.       v1 = hll_hash(k1),
  147.       v2 = hll_hash(k2),
  148.       v3 = empty_hll()
  149.     )
  150.     )
  151.     WITH BROKER hdfs ("username"="hdfs_user", "password"="hdfs_password");
  153.     LOAD LABEL example_db.label8
  154.     (
  155.     DATA INFILE("hdfs://hdfs_host:hdfs_port/user/palo/data/input/file")
  156.     INTO TABLE `my_table`
  157.     PARTITION (p1, p2)
  158.     COLUMNS TERMINATED BY ","
  159.     (k1, k2, tmp_k3, tmp_k4, v1, v2)
  160.     SET (
  161.       v1 = hll_hash(tmp_k3),
  162.       v2 = hll_hash(tmp_k4)
  163.     )
  164.     )
  165.     WITH BROKER hdfs ("username"="hdfs_user", "password"="hdfs_password");
  167. 8. 导入Parquet文件中数据  指定FORMAT 为parquet， 默认是通过文件后缀判断
  169.     LOAD LABEL example_db.label9
  170.     (
  171.     DATA INFILE("hdfs://hdfs_host:hdfs_port/user/palo/data/input/file")
  172.     INTO TABLE `my_table`
  173.     FORMAT AS "parquet"
  174.     (k1, k2, k3)
  175.     )
  176.     WITH BROKER hdfs ("username"="hdfs_user", "password"="hdfs_password");
  178. 9. 提取文件路径中的分区字段
  180.     如果需要，则会根据表中定义的字段类型解析文件路径中的分区字段（partitioned fields），类似Spark中Partition Discovery的功能
  182.     LOAD LABEL example_db.label10
  183.     (
  184.     DATA INFILE("hdfs://hdfs_host:hdfs_port/user/palo/data/input/dir/city=beijing/*/*")
  185.     INTO TABLE `my_table`
  186.     FORMAT AS "csv"
  187.     (k1, k2, k3)
  188.     COLUMNS FROM PATH AS (city, utc_date)
  189.     SET (uniq_id = md5sum(k1, city))
  190.     )
  191.     WITH BROKER hdfs ("username"="hdfs_user", "password"="hdfs_password");
  193.     hdfs://hdfs_host:hdfs_port/user/palo/data/input/dir/city=beijing目录下包括如下文件：
  195.     [hdfs://hdfs_host:hdfs_port/user/palo/data/input/dir/city=beijing/utc_date=2019-06-26/0000.csv, hdfs://hdfs_host:hdfs_port/user/palo/data/input/dir/city=beijing/utc_date=2019-06-26/0001.csv, ...]
  197.     则提取文件路径的中的city和utc_date字段
  199. 10. 对待导入数据进行过滤，k1 值大于 k2 值的列才能被导入
  201.     LOAD LABEL example_db.label10
  202.     (
  203.     DATA INFILE("hdfs://hdfs_host:hdfs_port/user/palo/data/input/file")
  204.     INTO TABLE `my_table`
  205.     where k1 > k2
  206.     )
  208. 11. 从 AmazonS3 导入Parquet文件中数据，指定 FORMAT 为parquet，默认是通过文件后缀判断：
  210.     LOAD LABEL example_db.label11
  211.     (
  212.     DATA INFILE("s3a://my_bucket/input/file")
  213.     INTO TABLE `my_table`
  214.     FORMAT AS "parquet"
  215.     (k1, k2, k3)
  216.     )
  217.     WITH BROKER my_s3a_broker
  218.     (
  219.     "fs.s3a.access.key" = "xxxxxxxxxxxxxxxxxxxxxxxxxx",
  220.     "fs.s3a.secret.key" = "yyyyyyyyyyyyyyyyyyyy",
  221.     "fs.s3a.endpoint" = "s3.amazonaws.com"
  222.     )
  224. 12. 提取文件路径中的时间分区字段，并且时间包含 %3A (在 hdfs 路径中，不允许有 ':'，所有 ':' 会由 %3A 替换)
  226.     假设有如下文件：
  228.     /user/data/data_time=2020-02-17 00%3A00%3A00/test.txt
  229.     /user/data/data_time=2020-02-18 00%3A00%3A00/test.txt
  231.     表结构为：
  232.     data_time DATETIME,
  233.     k2        INT,
  234.     k3        INT
  236.     LOAD LABEL example_db.label12
  237.     (
  238.      DATA INFILE("hdfs://host:port/user/data/*/test.txt") 
  239.      INTO TABLE `tbl12`
  240.      COLUMNS TERMINATED BY ","
  241.      (k2,k3)
  242.      COLUMNS FROM PATH AS (data_time)
  243.      SET (data_time=str_to_date(data_time, '%Y-%m-%d %H%%3A%i%%3A%s'))
  244.     ) 
  245.     WITH BROKER "hdfs" ("username"="user", "password"="pass");

keyword

  1. BROKER,LOAD