Have you ever worked with native JSON documents in SAP HANA Cloud? If the answer is 'No', you can find some resources to get started here:
-- creating a collection
CREATE COLLECTION Customers;
-- insert record using the Object Expression interface
INSERT INTO Customers VALUES(
{
"name": 'Paul',
"address": {
"street": 'Hauptstraße 10',
"city": 'Heidelberg'
}
});
-- insert record using the string interface
-- use this if you have a JSON string already in your application; notice the different quotes
INSERT INTO Customers VALUES('
{
"name": "Paul",
"address": {
"street": "Hauptstraße 10",
"city": "Heidelberg"
}
}');
-- Query with filter
SELECT * FROM Customers WHERE "address"."city" = 'Heidelberg';
-- Query with projection and aggregation
SELECT "address"."city", COUNT(*) FROM Customers GROUP BY "address"."city";
INSERT INTO mycollection VALUES ('<some json doc in form of a single-quoted string>')
INSERT INTO mycollection VALUES ({"<some json-like object, that is understood by the SQL layer>"})
INSERT INTO mycollection VALUES ({"name": 'henry'})
SELECT {"surname": "name", "justthestreet": "address"."street"} FROM Customers
CREATE COLUMN TABLE SomeColumnStoreTable ("name" nvarchar(20), "age" int);
INSERT INTO SomeColumnStoreTable VALUES('Paul', 34);
WITH myView AS (SELECT "name", "address"."city" FROM Customers) -- implicit view!
SELECT * FROM myView INNER JOIN SomeColumnStoreTable AS cst ON myView."name" = cst."name";
create collection clubs;
create collection players;
insert into clubs values({"clubName": 'Heidelberg', "league": 'X'});
insert into players values({"playerName": 'Paul', "club": 'Heidelberg'});
with c as (select "clubName", "league" from clubs),
p as (select "playerName" , "club" from players)
select * from c inner join p
on c."clubName" = p."club";
CREATE COLLECTION mycol;
INSERT INTO mycol VALUES('{"a":1, "b":1}');
INSERT INTO mycol VALUES('{"a":1, "b":2}');
INSERT INTO mycol VALUES('{"a":2, "b":1}');
INSERT INTO mycol VALUES('{"a":2, "b":2}');
INSERT INTO mycol VALUES('{"a":3, "b":1}');
INSERT INTO mycol VALUES('{"a":3, "b":2}');
DO BEGIN
DECLARE v_int int;
select max("a") into v_int from mycol;
select * from mycol where "a" = bind_as_value(:v_int);
end;
-- direct index access
SELECT "array"[2] FROM MYCOLLECTION;
-- access via prepared statement
SELECT "array"[?] FROM MYCOLLECTION;
-- access via SQL variable
DO BEGIN
DECLARE n INT; DECLARE i INT;
SELECT CARDINALITY("array") INTO n FROM COLLECTION_WITH_ARRAY WHERE "id" = 0;
FOR i IN 1 .. :n DO
UPDATE COLLECTION_WITH_ARRAY SET "array"[:i] = 'visited' WHERE "id" = 0;
END FOR;
SELECT * FROM COLLECTION_WITH_ARRAY;
END;
SELECT CARDINALITY("array") FROM MYCOLLECTION;
--Filter a document if any of the element of an array satisfies a predicate
SELECT "id", "array"
FROM COLLECTION_WITH_ARRAY
WHERE
FOR ANY "item" IN "array" SATISFIES
"item"."id" < "id"
END;
--For any Filter can be nested:
SELECT "id", "array"
FROM COLLECTION_WITH_ARRAY
WHERE
FOR ANY "item" IN "array" SATISFIES
FOR ANY "item2" IN "item" SATISFIES
"item2"."id" < "id"
END
END;
-- Independent UNNESTs
SELECT "m", "n"
FROM COLLECTION_WITH_ARRAY
UNNEST "array" as "m"
UNNEST "array2" as "n"
WHERE
"m" = 3 AND "n"[1] = 1;
-- Dependent UNNESTs
SELECT "m", "n", "n2"
FROM COLLECTION_WITH_ARRAY
UNNEST "array" as "m"
UNNEST "array2" as "n"
UNNEST "n" as "n2"
WHERE
"m" = 3 AND "n2" = 6;
from hana_ml.docstore import create_collection_from_elements
with open("./path/to/document.json") as json_file:
data = json.load(json_file)
create_collection_from_elements(
connection_context=connection_context,
collection_name="test_collection",
elements=data,
drop_exist_coll=True,
)
CREATE COLLECTION mycol;
IMPORT FROM JSON FILE 's3-<AWS S3 region>://<AWS S3 Access Key ID>:<AWS S3 Secret Access Key>@<AWS S3 bucket>/<aws-object-id>'
INTO mycol WITH BATCH 10000 THREADS 10 FAIL ON INVALID DATA;
EXPORT INTO JSON FILE 's3-<AWS S3 region>://<AWS S3 Access Key ID>:<AWS S3 Secret Access Key>@<AWS S3 bucket>/<aws-object-id>'
FROM mycol WITH THREADS 10;
try(final Connection connection = ds.getConnection(); final Statement pstmt = connection.prepareStatement("INSERT INTO collectionName VALUES(?)")) {
connection.setAutoCommit(true);
pstmt.setString(1, "{"productid": 6060, "price": 735.8228858436846}");
pstmt.addBatch();
pstmt.setString(1, "{"productid": 7090, "price": 735.8228858436846}");
pstmt.addBatch();
pstmt.setString(1, "{"productid": 9087, "price": 735.8228858436846}");
pstmt.addBatch();
pstmt.executeBatch();
}
data = []
data.append(['{"key": 15, "key2": 100}'])
data.append(['{"key": 15, "key2": 101}'])
data.append(['{"key": 15, "key2": 102}'])
cursor.executemany("""insert into MyCollection values(?)""", data)
CREATE COLLECTION c1;
CREATE SEQUENCE s1;
INSERT INTO c1 VALUES('{"KEY":' || s1.nextval || '}');
SELECT * FROM c1;
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
11 | |
10 | |
10 | |
10 | |
8 | |
7 | |
7 | |
7 | |
7 | |
6 |