2021.10
Collibra DIC Integration
Powered By GitBook
Export and Import Example
Best practice is to use get-exports and the owl_rule table post 2021.09 release. Please refer to the Export and Import API page for more details.

Steps

    1.
    Find your dataset
    2.
    Pass your table to the following api call - http://<url>/v2/get-rules-export?dataset=public.transit_6
    3.
    Run import on the desired environment, passing the output of the previous statement to the body of the request - http://<url>/v2/run-import
The following function needs to be declared in the postgres metastore before this can run.
1
CREATE OR REPLACE FUNCTION public.dump(p_schema text, p_table text, p_where text)
2
RETURNS SETOF text
3
LANGUAGE plpgsql
4
AS $function$
5
DECLARE
6
dumpquery_0 text;
7
dumpquery_1 text;
8
selquery text;
9
selvalue text;
10
valrec record;
11
colrec record;
12
BEGIN
13
14
-- ------ --
15
-- GLOBAL --
16
-- build base INSERT
17
-- build SELECT array[ ... ]
18
dumpquery_0 := 'INSERT INTO ' || quote_ident(p_schema) || '.' || quote_ident(p_table) || '(';
19
selquery := 'SELECT array[';
20
21
<<label0>>
22
FOR colrec IN SELECT table_schema, table_name, column_name, data_type
23
FROM information_schema.columns
24
WHERE table_name = p_table and table_schema = p_schema
25
ORDER BY ordinal_position
26
LOOP
27
dumpquery_0 := dumpquery_0 || quote_ident(colrec.column_name) || ',';
28
selquery := selquery || 'CAST(' || quote_ident(colrec.column_name) || ' AS TEXT),';
29
END LOOP label0;
30
31
dumpquery_0 := substring(dumpquery_0 ,1,length(dumpquery_0)-1) || ')';
32
dumpquery_0 := dumpquery_0 || ' VALUES (';
33
selquery := substring(selquery ,1,length(selquery)-1) || '] AS MYARRAY';
34
selquery := selquery || ' FROM ' ||quote_ident(p_schema)||'.'||quote_ident(p_table);
35
selquery := selquery || ' WHERE '||p_where;
36
-- GLOBAL --
37
-- ------ --
38
39
-- ----------- --
40
-- SELECT LOOP --
41
-- execute SELECT built and loop on each row
42
<<label1>>
43
FOR valrec IN EXECUTE selquery
44
LOOP
45
dumpquery_1 := '';
46
IF not found THEN
47
EXIT ;
48
END IF;
49
50
-- ----------- --
51
-- LOOP ARRAY (EACH FIELDS) --
52
<<label2>>
53
FOREACH selvalue in ARRAY valrec.MYARRAY
54
LOOP
55
IF selvalue IS NULL
56
THEN selvalue := 'NULL';
57
ELSE selvalue := quote_literal(selvalue);
58
END IF;
59
dumpquery_1 := dumpquery_1 || selvalue || ',';
60
END LOOP label2;
61
dumpquery_1 := substring(dumpquery_1 ,1,length(dumpquery_1)-1) || ');';
62
-- LOOP ARRAY (EACH FIELD) --
63
-- ----------- --
64
65
-- debug: RETURN NEXT dumpquery_0 || dumpquery_1 || ' --' || selquery;
66
-- debug: RETURN NEXT selquery;
67
RETURN NEXT dumpquery_0 || dumpquery_1;
68
69
END LOOP label1 ;
70
-- SELECT LOOP --
71
-- ----------- --
72
73
RETURN ;
74
END
75
$function$
76
;
77
Copied!

From Swagger

Navigate to the API page
Find the Rest APIs link
Drill-in to the controller-scala section
Find the get-rules-export call
Click Try it out and enter a dataset name, Execute to run the call
Copy the response body
Navigate to the controller-catalog section
Find run-import and Try it out
Make any edits and paste in the response body from the previous step
Visually validate the rules were transferred to another dataset successfully
Last modified 14d ago
Copy link