All Products
Search
Document Center

Background and preparations

Last Updated: Aug 05, 2019

Background

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.

Example

Prerequisites

  1. You have uploaded the pivot and unpiovt tables that contain test data to OSS, and stored them in the paths of oss://bucket-name/test/pivot/pivot.txt and oss:/bucket-name/test/unpivot/unpivot.txt, respectively.

    1

  1. You have created an OSS schema.

    1. CREATE SCHEMA oss_test_schema with DBPROPERTIES(
    2. catalog='oss',
    3. location = 'oss://bucket-name/test/'
    4. );
  2. You have created OSS tables.

    1. CREATE EXTERNAL TABLE pivot (
    2. id int,
    3. username string,
    4. subject string,
    5. score int
    6. )
    7. ROW FORMAT DELIMITED
    8. FIELDS TERMINATED BY ','
    9. STORED AS TEXTFILE
    10. LOCATION 'oss://bucket-name/test/pivot/'
    1. CREATE EXTERNAL TABLE unpivot (
    2. username string,
    3. yuwen int,
    4. shuxue int,
    5. yingyu int
    6. )
    7. ROW FORMAT DELIMITED
    8. FIELDS TERMINATED BY ','
    9. STORED AS TEXTFILE
    10. LOCATION 'oss://dlaossfile1/test/unpivot/'
  3. You have queried data in the tables.

    1. SELECT * FROM oss_test_schema.pivot

    Data in the pivot table

    1. SELECT * FROM oss_test_schema.unpivot

    Data in unpivot table