Row-to-column conversion (Pivot) and column-to-row conversion (Unpivot) are often required for data processing with SQL statements. This topic provides examples of SQL statements to demonstrate how to convert rows into columns and columns into rows in Data Lake Analytics (DLA). In addition, DLA supports JSON functions and UNNEST syntax, which allows you to process data more easily and flexibly by using SQL statements.
Prerequisites
-
You have uploaded the pivot and unpiovt tables that contain test data to OSS, and stored them in the paths of
oss:///testBucketName/test/pivot/pivot.txt
andoss://testBucketName/test/unpivot/unpivot.txt
, respectively.
-
You have created an OSS schema.
CREATE SCHEMA oss_test_schema with DBPROPERTIES( catalog='oss', location = 'oss://testBucketName/test/' );
-
You have created OSS tables.
CREATE EXTERNAL TABLE pivot ( id int, username string, subject string, score int ) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' STORED AS TEXTFILE LOCATION 'oss://testBucketName/test/pivot/'
CREATE EXTERNAL TABLE unpivot ( username string, yuwen int, shuxue int, yingyu int ) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' STORED AS TEXTFILE LOCATION 'oss://testBucketName/test/unpivot/'
-
You have queried data in the tables.
SELECT * FROM oss_test_schema.pivot
SELECT * FROM oss_test_schema.unpivot