πŸŽ“
$dump-sql tutorial
In this tutorial, you will see how to use $dump-sql to export data for analytics
​
Create Client & AccessPolicy for your API agent - in our case curl
1
PUT /
2
Accept: text/yaml
3
Content-Type: text/yaml
4
​
5
- resourceType: Client
6
id: bulk-client
7
secret: secret
8
grant_types: ['basic']
9
- resourceType: AccessPolicy
10
id: bulk-client
11
engine: allow
12
link:
13
- {id: 'bulk-client', resourceType: 'Client'}
Copied!
Generate some number of patients using SQL - in DB Console:
1
INSERT INTO patient (id,txid, status, resource)
2
SELECT g.id, g.id, 'created', '{"name": [{"family": "John"}]}'
3
FROM generate_series(1, 100000) AS g (id);
4
--
5
SELECT count(*) FROM Patient;
Copied!
Now we can test bulk export using $dump operation with curl program:
1
curl -u bulk-client:secret /Patient/\$dump > /tmp/pt.ndjson
2
​
3
> % Total % Received % Xferd Average Speed Time Time Time Current
4
> Dload Upload Total Spent Left Speed
5
> 100 12.0M 0 12.0M 0 0 9527k 0 --:--:-- 0:00:01 --:--:-- 9523k
6
​
7
less /tmp/pt.ndjson
Copied!
We got 100K patients in less than a second!
Do not forget to clean up the database:
1
TRUNCATE Patient;
Copied!

Load data into BigQuery

1
# load tsv data
2
curl -v -X POST -u bulk-client:secret -H 'content-type:application/json' \
3
https://<YOURBOX>/\$dump-sql \
4
-d '{"query": "select id, ts, resource#>>'"'"'{module}'"'"' from entity"}' \
5
> data.tsv
6
​
7
# create dataset
8
bq mk test
9
bq ls
10
​
11
# load datast
12
bq load --source_format=CSV \
13
--field_delimiter=tab \
14
test.entities ./data.tsv res,ts,mod
15
​
16
# list ids
17
bq query 'select id from test.entities'
18
​
19
# remove dataset
20
bq rm -r test
Copied!
Last modified 10mo ago
Copy link