Formsort
Search…
BigQuery
Stream form answers into Google BigQuery.
The Google BigQuery integration uses the BigQuery streaming API to insert answers into a BigQuery table, as they are received.
BiqQuery billing
The BigQuery streaming API is not available in the Google Cloud's free tier. See pricing.

Authentication

BigQuery is authenticated using a service account. Once a service account is created, export the credentials as a keyfile, and upload the keyfile to Formsort.
Note that the service account needs to have the ability to write data into your created table, such as the bigquery.dataEditor predefined role.

Table schema

To reduce security footprint, you must create the table to which formsort can write.
Since the schema of a form flow can change between different deployments, as new questions are added and removed, the schema for writing to a BigQuery table has a repeated answers field that stores the answer values received.
Field name
Type
Mode
Description
submitted_at
TIMESTAMP
REQUIRED
UTC timestamp of when the answers were received by Formsort.
responder_uuid
STRING
REQUIRED
flow_label
STRING
REQUIRED
The flow label.
variant_label
STRING
REQUIRED
The variant label.
variant_revision_uuid
STRING
REQUIRED
The variant revision UUID under which these answers were collected.
event_type
STRING
REQUIRED
Which analytics event this payload corresponds to.
This is currently either StepCompleted, or FlowFinalized.
answers
RECORD
REPEATED
Fields:
  • key
    • STRING, REQUIRED
    • The answer variable label
  • value
    • STRING, REQUIRED
    • The value of the answer, as a string
  • type
    • STRING, NUMERIC, or BOOLEAN
schema_version
NUMERIC
REQUIRED
The version of the BigQuery adapter in use.
Currently at 2

Schema definition in JSON

To create the table within the BigQuery console, we recommend you copy-paste the following JSON schema using the Edit as text toggle:
1
[
2
{
3
"name": "submitted_at",
4
"type": "TIMESTAMP",
5
"mode": "REQUIRED"
6
},
7
{
8
"name": "responder_uuid",
9
"type": "STRING",
10
"mode": "REQUIRED"
11
},
12
{
13
"name": "flow_label",
14
"type": "STRING",
15
"mode": "REQUIRED"
16
},
17
{
18
"name": "variant_label",
19
"type": "STRING",
20
"mode": "REQUIRED"
21
},
22
{
23
"name": "variant_revision_uuid",
24
"type": "STRING",
25
"mode": "REQUIRED"
26
},
27
{
28
"name": "event_type",
29
"type": "STRING",
30
"mode": "REQUIRED"
31
},
32
{
33
"name": "answers",
34
"type": "RECORD",
35
"mode": "REPEATED",
36
"fields": [
37
{
38
"name": "key",
39
"type": "STRING",
40
"mode": "REQUIRED"
41
},
42
{
43
"name": "value",
44
"type": "STRING",
45
"mode": "REQUIRED"
46
},
47
{
48
"name": "type",
49
"type": "STRING",
50
"mode": "REQUIRED"
51
}
52
]
53
},
54
{
55
"name": "schema_version",
56
"type": "NUMERIC",
57
"mode": "REQUIRED"
58
}
59
]
Copied!

Schema definition in python

1
from google.cloud import bigquery
2
3
schema = [
4
bigquery.SchemaField("submitted_at", "TIMESTAMP", mode="REQUIRED"),
5
bigquery.SchemaField("responder_uuid", "STRING", mode="REQUIRED"),
6
bigquery.SchemaField("flow_label", "STRING", mode="REQUIRED"),
7
bigquery.SchemaField("variant_label", "STRING", mode="REQUIRED"),
8
bigquery.SchemaField("variant_revision_uuid", "STRING", mode="REQUIRED"),
9
bigquery.SchemaField("event_type", "STRING", mode="REQUIRED"),
10
bigquery.SchemaField(
11
"answers",
12
"RECORD",
13
mode="REPEATED",
14
fields=[
15
bigquery.SchemaField("key", "STRING", mode="REQUIRED"),
16
bigquery.SchemaField("value", "STRING", mode="REQUIRED"),
17
bigquery.SchemaField("type", "STRING", mode="REQUIRED"),
18
],
19
),
20
bigquery.SchemaField("schema_version", "NUMERIC", mode="REQUIRED"),
21
]
Copied!

Last modified 6mo ago