This example shows how to compose an entire table to a DATASET data type.
CREATE TABLE employeeTable( empID INTEGER, empName VARCHAR(100), empDept VARCHAR(100)); INSERT INTO employeeTable(1,'George Smith','Accounting'); INSERT INTO employeeTable(2,'Pauline Kramer','HR'); INSERT INTO employeeTable(3,'Steven Mazzo','Engineering'); SELECT * FROM DATASET_PUBLISH ( ON (SELECT empName, empDept FROM employeeTable) ) AS avroFiles; data 7B2274797065223A226172726179222C226974656D73223A7B2274797065223A227265636F7264222C226E616D65223A227265635F30222C226669656C6473223A5B7B226E616D65223A22656D704E616D65222C2274797065223A22737472696E67227D2C7B226E616D65223A22656D7044657074222C2274797065223A22737472696E67227D5D7D7D0006001847656F72676520536D697468144163636F756E74696E67021C5061756C696E65204B72616D6572044852041853746576656E204D617A7A6F16456E67696E656572696E6700
To compose an entire table to a DATASET data type, with the storage format CSV:
CREATE TABLE employeeTable( empID INTEGER, empName VARCHAR(100), empDept VARCHAR(100)); INSERT INTO employeeTable(1,'George Smith','Accounting'); INSERT INTO employeeTable(2,'Pauline Kramer','HR'); INSERT INTO employeeTable(3,'Steven Mazzo','Engineering'); SELECT * FROM DATASET_PUBLISH ( ON (SELECT empName, empDept FROM employeeTable) RETURNS (data DATASET STORAGE FORMAT CSV) ) AS csvFiles;
fileSchema | fileData |
---|---|
Null | empName,empDept George Smith,Accounting Pauline Kramer,HR Steven Mazzo,Engineering |
To convert the output to JSON, run the following examples using 'SELECT data.toJSON()' instead of 'SELECT *'.