Skip to main content Link Menu Expand (external link) Document Search Copy Copied

BULK INSERT example using NDJSON formatted data source

Insert data into a FeatureBase table from a new line delimited JSON data source on a remote file system.

Before you begin

Run SHOW TABLE <tablename> to learn the structure of a destination table

BULK INSERT statement 

BULK INSERT
  INTO ndjson-target (
    _id,
    type,
    actor_id,
    actor_login,
    actor_url,
    repo_id,
    repo_name,
    repo_url,
    payload_ref,
    payload_ref_type,
    payload_master_branch,
    payload_description,
    payload_pusher_type,
    public,
    created_at
)
  MAP (
    '$.id' ID,
    '$.type' STRING,
    '$.actor.id' ID,
    '$.actor.login' STRING,
    '$.actor.url' STRING,
    '$.repo.id' ID,
    '$.repo.name' STRING,
    '$.repo.url' STRING,
    '$.payload.ref' STRING,
    '$.payload.ref_type' STRING,
    '$.payload.master_branch' STRING,
    '$.payload.description' STRING,
    '$.payload.pusher_type' STRING,
    '$.public' BOOL,
    '$.created_at' TIMESTAMP
  )
  TRANSFORM(
    @0,
    @1,
    @2,
    @3,
    @4,
    @5,
    @6,
    @7,
    @8,
    @9,
    @10,
    @11,
    @12,
    CAST(@13 as BOOL),
    @14
  )
  FROM
    'https://featurebase-public-data.s3.us-east-2.amazonaws.com/github-2015-data.json'
  WITH
    BATCHSIZE 10000
    FORMAT 'NDJSON'
    INPUT 'URL'
    ALLOW_MISSING_VALUES;

Arguments

Argument Description Additional information
BULK INSERT INTO Insert data to the ndjson-target table <column-list> which is required by the MAP clause  
MAPclause String expression mapping source data to target <column-list>  
TRANSFORM clause Transform each value from data source to <column-list> and converts source public column to BOOL value using CAST function CAST function
FROM clause URL to ndjson data source  
WITH clause States the number of rows to batch, data source FORMAT, the method of INPUT and the statement will ALLOW_MISSING_VALUES from the data source  

Next step