Using Mapping with JSON files

Examples

The following examples use a JSON key pair and an array from a local file source.

SQL

CREATE TABLE keypairs(`key` VARCHAR(10), `value` VARCHAR(10)); 
CREATE PIPELINE jtest AS
LOAD DATA FS '<file path>/keypairs.json'
INTO TABLE keypairs
FORMAT JSON(`key` <- keypairs::`key`,`value` <- keypairs::`value`); 

START PIPELINE jtest;

SELECT * FROM keypairs;

+------+--------+
| key  | value  | 
+------+--------+
|    1 | 1      |
+------+--------+

Warning

To use reserved words in a table, backticks are required. The example above uses key and value as column names by using backticks.

SQL

CREATE TABLE teams(basketball VARCHAR(50), baseball VARCHAR (50), 
football VARCHAR(50), hockey VARCHAR(50));

CREATE PIPELINE teams_list AS
LOAD DATA FS '<file path>/jtinsert.json'
INTO TABLE teams
FORMAT JSON
(basketball <- teams::basketball,
baseball <- teams::baseball,
football <- teams::football,
hockey <- teams::hockey);

START PIPELINE teams_list;

SELECT * FROM teams;

+------------+-----------+----------+--------+
| basketball | baseball  | football | hockey |
+------------+-----------+----------+--------+
|  Lakers    | Dodgers   | Raiders  | Kings  |
+------------+-----------+----------+--------+

The following examples use a JSON key pair and an array from an S3 bucket.

SQL

CREATE TABLE keypairs(`key` VARCHAR(10), `value` VARCHAR (10));

CREATE PIPELINE jtest AS
LOAD DATA S3 's3://<bucket_name>/<filename>.json'
CONFIG '{"region":"us-west-2"}'
CREDENTIALS '{"aws_access_key_id": "XXXXXXXXXXXXXXXXX",               
              "aws_secret_access_key": "XXXXXXXXXXX"}'
INTO TABLE keypairs
FORMAT JSON
(`key` <- keypairs::`key`,
`value` <- keypairs::`value`);

START PIPELINE jtest;
SELECT * FROM keypairs;

+------+--------+
| key  | value  | 
+------+--------+
|    1 | 1      |
+------+--------+

SQL

CREATE TABLE teams(basketball varchar(50), baseball varchar (50), 
football varchar(50), hockey varchar(50));

Below is the contents of the JSON file used in the pipeline example:

JSON

{
	"teams": [{
			"basketball": "Knicks"
		},
		{
			"baseball": "Yankees"
		},
		{
			"football": "Giants"
		},
		{
			"hockey": "Rangers"
		}
	]
}

SQL

CREATE PIPELINE teams_list AS 
LOAD DATA S3 's3://<bucket_name>/<filename>.json'
CONFIG '{"region":"us-west-2"}'
CREDENTIALS '{"aws_access_key_id": "XXXXXXXXXXXXXXXXX",               
              "aws_secret_access_key": "XXXXXXXXXXX"}'
INTO TABLE teams
FORMAT JSON
(basketball <- teams::basketball,
baseball <- teams::baseball,
football <- teams::football,
hockey <- teams::hockey);

START PIPELINE teams_list;
SELECT * FROM teams;

+------------+-----------+----------+--------+
| basketball | baseball  | football | hockey |
+------------+-----------+----------+--------+
|  Lakers    | Dodgers   | Raiders  | Kings  |
+------------+-----------+----------+--------+

Using Mapping with JSON files

On this page

Examples

Was this article helpful?

On this page

Was this article helpful?