This topic describes how to create tables for TSV data in DLA.
Prerequisites
Before creating tables, you must create an OSS schema first. In the example, all tables use the following OSS schema:
CREATE SCHEMA dla_oss_db with DBPROPERTIES(
catalog='oss',
location 'oss://dlaossfile1/dla/'
);
Single character-separated files
TSV files are also plain text files. Columns are separated by tabs.
For example, the OSS instance has a file oss://dlaossfile1/dla/tsv_single.tsv
whose columns are separated by tabs. It stores the following data:
Beijing China 010
ShangHai China 021
Tianjin China 022
The following is a sample statement for creating a table based on the file:
CREATE EXTERNAL TABLE tsv_single (
city STRING,
country STRING,
code INT
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
STORED AS TEXTFILE
LOCATION 'oss://dlaossfile1/dla/';
The query result is as follows:
SELECT * FROM dla_oss_db.tsv_single
|----------|--------------|
| city |country| code |
|----------|----- |-----|
| Beijing | China| 10 |
| ShangHai | China| 21 |
| Tianjin | China| 22 |
Multi character-separated files
Separators of fields in TSV files can contain multiple characters. For example, fields of each column in tsv_multidelimit.tsv are separated by ||
. ||
can be replaced by a specific separator string.
https://farm7.staticflickr.com/5769/21094803716_da3cea21b8_o.jpg||7825604||EEGQ0ZQ/OxJAIGzfJ6LMvw==
https://c1.staticflickr.com/4/3743/11539980784_b52f835317_o.jpg||4227077||oc+H0KgW0sxyEgAlfOiQDQ==
The following is a sample statement for creating a table based on the file:
CREATE EXTERNAL TABLE tsv_multidelimit (
image_address STRING,
id STRING,
name STRING
)
ROW FORMAT SERDE 'org.apache.hadoop.hive.contrib.serde2.MultiDelimitSerDe'
with serdeproperties(
"field.delim"="||"
)
STORED AS TEXTFILE LOCATION 'oss://dlaossfile1/dla/';
The query result is as follows:
SELECT * FROM dla_oss_db.tsv_multidelimit
|-----------------------------------------------------------------|---|--------|
| image_address | id| name |
| https://farm7.staticflickr.com/5769/21094803716_da3cea21b8_o.jpg
| 7825604| EEGQ0ZQ/OxJAIGzfJ6LMvw==|
| https://c1.staticflickr.com/4/3743/11539980784_b52f835317_o.jpg
| 4227077| oc+H0KgW0sxyEgAlfOiQDQ==|