/insert/records/json

URL: http://GPUDB_IP_ADDRESS:GPUDB_PORT/insert/records/json

Overview

Kinetica provides a direct JSON ingest service to allow raw JSON to be translated into records and inserted into the database.

If the target table exists, inserted records will be appended to it.

If the target table does not exist, it will be created automatically, with type inferencing used to determine column types & sizes, before inserting data.

Either a single JSON object or multiple JSON objects (as an array) can be ingested at once:

Single Record (Object) 
1
2
3
4
5
6
7

{
    "id": "14",
    "category": "Technology",
    "name": "DVDs",
    "description": "Lightweight storage for low-res screenplays and licensed software",
    "stock": "5000"
}
Multiple Records (Array of Objects) 
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16

[
    {
        "id": "1",
        "category": "Furniture",
        "name": "Clamp-on Lamps",
        "description": "Strong, steadfast fasteners; localized lighting for laboring",
        "stock": "10"
    },
    {
        "id": "14",
        "category": "Technology",
        "name": "DVDs",
        "description": "Lightweight storage for low-res screenplays and licensed software",
        "stock": "5000"
    }
]

Usage

The base JSON ingest endpoint URL is:

http://<db.host>:9191/insert/records/json

The JSON payload should be passed as data in the request.

Authentication credentials should be passed with the request, as well.

Endpoint-specific options can be passed as request parameters. See /insert/records/frompayload under the option parameter for a complete list of supported options.

Note

Both columns_to_load & columns_to_skip can only used named columns, not column positions, as JSON maps don't lend themselves to positional references.

Examples

Below are templates of ingesting a JSON file of product data into a table, via the /insert/records/json REST endpoint:

cURL 
 1
 2
 3
 4
 5
 6
 7
 8
 9
10

KINETICA_URL=http://localhost:9191
USERNAME=auser
PASSWORD=apassword
TABLE_NAME=product
JSON_FILE_NAME=products.json

curl -sS -X POST --header "Content-Type: application/json" \
--user "${USERNAME}:${PASSWORD}" \
-d @${JSON_FILE_NAME} \
${KINETICA_URL}/insert/records/json?table_name=${TABLE_NAME}
Python 
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

kinetica_url = "http://localhost:9191"
username = "auser"
password = "apassword"
table_name = "product"
json_file_name = "products.json"

response = requests.post(
    kinetica_url + "/insert/records/json",
    params = {"table_name": table_name},
    data = open(json_file_name,"r").read(),
    auth = (username, password)
)

Endpoint-specific options can be passed as request parameters:

cURL 
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

KINETICA_URL=http://localhost:9191
USERNAME=auser
PASSWORD=apassword
OPTS_TABLE_NAME=product_options
JSON_FILE_NAME=products.json

# Quote the URL when passing multiple options, or the & will stop the URL
#   parsing and run the URL parsed up to that point as a background job
curl -sS -X POST --header "Content-Type: application/json" \
--user "${USERNAME}:${PASSWORD}" \
-d @${JSON_FILE_NAME} \
"${KINETICA_URL}/insert/records/json?table_name=${OPTS_TABLE_NAME}&batch_size=10000&columns_to_load=name,category,description&truncate_table=true"
Python 
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17

kinetica_url = "http://localhost:9191"
username = "auser"
password = "apassword"
table_name = "product_options"
json_file_name = "products.json"

response = requests.post(
    kinetica_url + "/insert/records/json",
    params = {
        "table_name": table_name,
        "batch_size": "10000",
        "columns_to_load": "name,category,description",
        "truncate_table": "true"
    },
    data = open(json_file_name,"r").read(),
    auth = (username, password)
)

Data can also be in-lined with the command:

cURL 
1
2
3
4
5
6
7
8
9

KINETICA_URL=http://localhost:9191
USERNAME=auser
PASSWORD=apassword
TABLE_NAME=product

curl -sS -X POST --header "Content-Type: application/json" \
--user "${USERNAME}:${PASSWORD}" \
-d '{"id":"14", "category": "Tech", "name": "DVDs", "description": "Video discs", "stock": "5"}' \
${KINETICA_URL}/insert/records/json?table_name=${TABLE_NAME}
Python 
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

kinetica_url = "http://localhost:9191"
username = "auser"
password = "apassword"
table_name = "product"

response = requests.post(
    kinetica_url + "/insert/records/json",
    params = {"table_name": table_name},
    data = '{"id":"14", "category": "Tech", "name": "DVDs", "description": "Video discs", "stock": "5"}',
    auth = (username, password)
)