CODEWITHSUNDEEP
graphhana
Kuls
Kuls

Reputation: 61

How to create multiple vertex in SAP HANA Graph

I'm trying to create 2 (multiple) vertex in SAP HANA like -

Create two table for vertex ITEM and DATASET

CREATE COLUMN TABLE "GREEK_MYTHOLOGY"."ITEM" (
"ITEM_ID" VARCHAR(100) PRIMARY KEY,
"ITEM_NAME" VARCHAR(100)
);

CREATE COLUMN TABLE "GREEK_MYTHOLOGY"."DATASET" (
"DATASET_ID" VARCHAR(100) PRIMARY KEY,
"DATASET_NAME" VARCHAR(100)
);

And creating edge as REFERENCES

CREATE COLUMN TABLE "GREEK_MYTHOLOGY"."REFERENCES" (
"REF_ID" INT UNIQUE NOT NULL,
"SOURCE" VARCHAR(100) NOT NULL
REFERENCES "GREEK_MYTHOLOGY"."ITEM" ("ITEM_ID")
ON UPDATE CASCADE ON DELETE CASCADE,
"TARGET" VARCHAR(100) NOT NULL
REFERENCES "GREEK_MYTHOLOGY"."DATASET" ("DATASET_ID")
ON UPDATE CASCADE ON DELETE CASCADE,
"TYPE" VARCHAR(100)
);

Now I would like to connect both vertex (ITEM and DATASET) with edge REFERENCES like below

CREATE GRAPH WORKSPACE "GREEK_MYTHOLOGY"."GRAPH"
EDGE TABLE "GREEK_MYTHOLOGY"."DATASET"
SOURCE COLUMN "SOURCE" 
TARGET COLUMN "TARGET"
VERTEX TABLE "GREEK_MYTHOLOGY"."ITEM" KEY COLUMN "ITEM_ID"
VERTEX TABLE "GREEK_MYTHOLOGY"."DATASET"KEY COLUMN "DATASET_ID"
KEY COLUMN "REF_ID";

But it throws this exception at line VERTEX TABLE "GREEK_MYTHOLOGY"."DATASET"KEY COLUMN "DATASET_ID":

sql syntax error: incorrect syntax near "VERTEX": line 6 col 1 (at pos 200)

Is it possible to create multiple vertex in SAP HANA graph ? If yes then what is the right way to do this.

Upvotes: 0

Views: 432

Answers (1)

Lars Br.
Lars Br.

Reputation: 10388

There's a misunderstanding here. The REFERENCES clause in the CREATE TABLE statement has nothing to do with the graph structure you want to represent. Instead, it defines a foreign key constraint between the two tables.

The CREATE GRAPH WORKSPACE command only accepts one EDGE TABLE and one VERTEX TABLE as parameters. However, you can also pass in synonyms or views here.

That way, you could create a view "ALL_ITEMS" like this:

CREATE VIEW "GREEK_MYTHOLOGY"."ALL_ITEMS" as
  SELECT "ITEM_ID" as "ID", "ITEM_NAME" as "NAME"  FROM "GREEK_MYTHOLOGY"."ITEM"
  UNION 
  SELECT "DATASET_ID" as "ID", "DATASET_NAME" as "NAME" FROM "GREEK_MYTHOLOGY"."DATASET";

and then reference this view:

CREATE GRAPH WORKSPACE "GREEK_MYTHOLOGY"."GRAPH"
       EDGE TABLE "GREEK_MYTHOLOGY"."DATASET"
            SOURCE COLUMN "SOURCE" 
            TARGET COLUMN "TARGET"
       VERTEX TABLE "GREEK_MYTHOLOGY"."ALL_ITEMS"
            KEY COLUMN "NAME";

Using this approach is possible, but you now have to make sure that the "NAME" values are unique and not NULL across both tables.

Upvotes: 2

Related Questions