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

  1. 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 and oss://testBucketName/test/unpivot/unpivot.txt, respectively.

  1. You have created an OSS schema.

    CREATE SCHEMA oss_test_schema with DBPROPERTIES(
    catalog='oss',
   location = 'oss://testBucketName/test/'
   );

  2. 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/'

  3. You have queried data in the tables.

    SELECT * FROM oss_test_schema.pivot

    SELECT * FROM oss_test_schema.unpivot