본문으로 건너뛰기
최종 수정: 에

HDFS

Description​

The HDFS table-valued function (TVF) reads files on HDFS as a relational table. It supports csv, csv_with_names, csv_with_names_and_types, json, parquet, orc, and avro formats.

Syntax​

HDFS(
    "uri" = "<uri>",
    "fs.defaultFS" = "<fs_defaultFS>",
    "hadoop.username" = "<hadoop_username>",
    "format" = "<format>"
    [, "<optional_property_key>" = "<optional_property_value>" [, ...] ]
)

Parameters​

Required Parameters​

ParameterDescription
uriHDFS URI of the file to read. If the path does not exist or the file is empty, the TVF returns an empty result.
fs.defaultFSDefault HDFS filesystem URI.
hadoop.usernameHDFS username. Provide a nonempty string.
formatFile format: csv, csv_with_names, csv_with_names_and_types, json, parquet, orc, or avro.

Optional Parameters​

Use the following keys for <optional_property_key> and their corresponding values for <optional_property_value>:

ParameterDescriptionNotes
hadoop.security.authenticationHDFS authentication type.
hadoop.kerberos.principalKerberos principal.
hadoop.kerberos.keytabKerberos keytab file.
dfs.client.read.shortcircuitEnable HDFS short-circuit reads.
dfs.domain.socket.pathDomain socket path.
dfs.nameservicesNameservice used in HA mode.
dfs.ha.namenodes.<nameservice>NameNode IDs for the specified nameservice in HA mode.
dfs.namenode.rpc-address.<nameservice>.<namenode>RPC address of the specified NameNode.
dfs.client.failover.proxy.provider.<nameservice>Failover proxy provider class for the nameservice.
column_separatorColumn separator; defaults to \t.
line_delimiterLine delimiter; defaults to \n.
compress_typeCompression: UNKNOWN, PLAIN, GZ, LZO, BZ2, LZ4FRAME, DEFLATE, or SNAPPYBLOCK. Defaults to UNKNOWN, which infers compression from the URI suffix.
read_json_by_lineRead JSON by line; defaults to true.JSON Load
strip_outer_arrayRemove the outer JSON array; defaults to false.JSON Load
json_rootJSON root path; empty by default.JSON Load
json_pathsJSON field paths; empty by default.JSON Load
num_as_stringRead JSON numbers as strings; defaults to false.JSON Load
fuzzy_parseEnable fuzzy JSON parsing; defaults to false.JSON Load
trim_double_quotesBoolean for CSV; defaults to false. When enabled, removes outer double quotes from each field.
skip_linesInteger for CSV; defaults to 0. Skips initial lines; ignored for csv_with_names and csv_with_names_and_types.
path_partition_keysComma-separated partition column names encoded in the path. For /path/to/city=beijing/date=2023-07-09, use city,date to extract those values.
resourceExisting HDFS Resource used to access HDFS. See CREATE RESOURCE.Since 2.1.4
enable_mapping_varbinaryIn the dev version, accepted only for compatibility and ignored: external binary data always maps to VARBINARY. Doris 4.1.5 keeps this TVF option configurable with a default of false.Since 4.0.3
enable_mapping_timestamp_tzSince 4.1.5, accepted only for compatibility; the value is ignored. Parquet Timestamp with isAdjustedToUTC=true and ORC TIMESTAMP_INSTANT always map to TIMESTAMPTZ, even when this option is false.Since 4.0.3
hive.parquet.time-zoneOptional string, empty by default. Specifies the Parquet INT96 writer time zone: an IANA time zone or UTC offset from -12:00 to +14:00; short aliases such as CST and PRC are rejected. See Parquet.Since 4.1.5

Access Control Requirements​

When using the resource parameter, the querying user needs USAGE on that Resource. Doris must also have network access to HDFS and valid HDFS or Kerberos credentials for the files.

Return Value​

Returns a table whose column names and types are inferred from the files. NULL fields are returned as SQL NULL. A nonexistent path or an empty file returns an empty result. Invalid parameters, inaccessible storage, or malformed files can cause analysis or execution to fail.

Examples​

The following examples assume that the HDFS file student.csv contains:

1,alice,18
2,bob,20
3,jack,24
4,jackson,19
5,liming,18

Read a CSV file​

SELECT * FROM HDFS(
    "uri" = "hdfs://127.0.0.1:8424/user/doris/csv_format_test/student.csv",
    "fs.defaultFS" = "hdfs://127.0.0.1:8424",
    "hadoop.username" = "doris",
    "format" = "csv",
    "column_separator" = ","
);
+------+---------+------+
| c1   | c2      | c3   |
+------+---------+------+
| 1    | alice   | 18   |
| 2    | bob     | 20   |
| 3    | jack    | 24   |
| 4    | jackson | 19   |
| 5    | liming  | 18   |
+------+---------+------+

Read a CSV file from HA HDFS​

Configure the nameservice and both NameNode addresses for your HA cluster. The URI and fs.defaultFS use the logical nameservice:

SELECT * FROM HDFS(
    "uri" = "hdfs://my_hdfs/user/doris/csv_format_test/student.csv",
    "fs.defaultFS" = "hdfs://my_hdfs",
    "hadoop.username" = "doris",
    "format" = "csv",
    "column_separator" = ",",
    "dfs.nameservices" = "my_hdfs",
    "dfs.ha.namenodes.my_hdfs" = "nn1,nn2",
    "dfs.namenode.rpc-address.my_hdfs.nn1" = "namenode01:8020",
    "dfs.namenode.rpc-address.my_hdfs.nn2" = "namenode02:8020",
    "dfs.client.failover.proxy.provider.my_hdfs" = "org.apache.hadoop.hdfs.server.namenode.ha.ConfiguredFailoverProxyProvider"
);
+------+---------+------+
| c1   | c2      | c3   |
+------+---------+------+
| 1    | alice   | 18   |
| 2    | bob     | 20   |
| 3    | jack    | 24   |
| 4    | jackson | 19   |
| 5    | liming  | 18   |
+------+---------+------+

Inspect the inferred schema​

DESC FUNCTION HDFS(
    "uri" = "hdfs://127.0.0.1:8424/user/doris/csv_format_test/student.csv",
    "fs.defaultFS" = "hdfs://127.0.0.1:8424",
    "hadoop.username" = "doris",
    "format" = "csv",
    "column_separator" = ","
);
+-------+------+------+-------+---------+-------+
| Field | Type | Null | Key   | Default | Extra |
+-------+------+------+-------+---------+-------+
| c1    | TEXT | Yes  | false | NULL    | NONE  |
| c2    | TEXT | Yes  | false | NULL    | NONE  |
| c3    | TEXT | Yes  | false | NULL    | NONE  |
+-------+------+------+-------+---------+-------+

Usage Notes​

For other common file options, see the S3 TVF. HDFS-specific authentication and HA settings are described in HDFS Storage.

Report issue