Special characters causing SQL compilation error (unexpected colon, :)
Special characters causing SQL compilation error, unexpected colon(:
). Used in fields like agent:family
, agent:os:version
It seems to put things just fine into the TMP_GITTER_CLIENT_ACCESS_EVENTS
table but when copying it to the real table GITTER_CLIENT_ACCESS_EVENTS
, I get the following error. I can see the field names with colons in Snowflake just fine, so it just seems to something wrong with the necessary escaping with the query itself.
I tried updating snowflake-sqlalchemy
but ran into, #9 (closed)
CRITICAL (snowflake.connector.errors.ProgrammingError) 001003 (42000): SQL compilation error:
CRITICAL syntax error line 1 at position 259 unexpected ':'.
CRITICAL syntax error line 1 at position 358 unexpected ':'.
CRITICAL syntax error line 1 at position 443 unexpected ':'.
CRITICAL syntax error line 1 at position 534 unexpected ':'.
CRITICAL syntax error line 1 at position 627 unexpected ':'.
CRITICAL syntax error line 1 at position 708 unexpected ':'.
CRITICAL syntax error line 1 at position 1,421 unexpected ':'. [SQL: 'MERGE INTO EEASTWOOD_SCRATCH.gitter_client_access_events USING EEASTWOOD_SCRATCH.TMP_GITTER_CLIENT_ACCESS_EVENTS ON EEASTWOOD_SCRATCH.gitter_client_access_events._id = EEASTWOOD_SCRATCH.TMP_GITTER_CLIENT_ACCESS_EVENTS._id WHEN MATCHED THEN UPDATE SET d__agent:device:family = EEASTWOOD_SCRATCH.TMP_GITTER_CLIENT_ACCESS_EVENTS.d__agent:device:family, d__agent:family = EEASTWOOD_SCRATCH.TMP_GITTER_CLIENT_ACCESS_EVENTS.d__agent:family, d__agent:os:family = EEASTWOOD_SCRATCH.TMP_GITTER_CLIENT_ACCESS_EVENTS.d__agent:os:family, d__agent:os:version = EEASTWOOD_SCRATCH.TMP_GITTER_CLIENT_ACCESS_EVENTS.d__agent:os:version, d__agent:type = EEASTWOOD_SCRATCH.TMP_GITTER_CLIENT_ACCESS_EVENTS.d__agent:type, d__agent:version = EEASTWOOD_SCRATCH.TMP_GITTER_CLIENT_ACCESS_EVENTS.d__agent:version, d__client_id = EEASTWOOD_SCRATCH.TMP_GITTER_CLIENT_ACCESS_EVENTS.d__client_id, d__client_key = EEASTWOOD_SCRATCH.TMP_GITTER_CLIENT_ACCESS_EVENTS.d__client_key, d__client_name = EEASTWOOD_SCRATCH.TMP_GITTER_CLIENT_ACCESS_EVENTS.d__client_name, d__env = EEASTWOOD_SCRATCH.TMP_GITTER_CLIENT_ACCESS_EVENTS.d__env, d__tag = EEASTWOOD_SCRATCH.TMP_GITTER_CLIENT_ACCESS_EVENTS.d__tag, d__user_id = EEASTWOOD_SCRATCH.TMP_GITTER_CLIENT_ACCESS_EVENTS.d__user_id, t = EEASTWOOD_SCRATCH.TMP_GITTER_CLIENT_ACCESS_EVENTS.t, time_extracted = EEASTWOOD_SCRATCH.TMP_GITTER_CLIENT_ACCESS_EVENTS.time_extracted WHEN NOT MATCHED THEN INSERT (_id, d__agent:device:family, d__agent:family, d__agent:os:family, d__agent:os:version, d__agent:type, d__agent:version, d__client_id, d__client_key, d__client_name, d__env, d__tag, d__user_id, t, time_extracted) VALUES (EEASTWOOD_SCRATCH.TMP_GITTER_CLIENT_ACCESS_EVENTS._id, EEASTWOOD_SCRATCH.TMP_GITTER_CLIENT_ACCESS_EVENTS.d__agent:device:family, EEASTWOOD_SCRATCH.TMP_GITTER_CLIENT_ACCESS_EVENTS.d__agent:family, EEASTWOOD_SCRATCH.TMP_GITTER_CLIENT_ACCESS_EVENTS.d__agent:os:family, EEASTWOOD_SCRATCH.TMP_GITTER_CLIENT_ACCESS_EVENTS.d__agent:os:version, EEASTWOOD_SCRATCH.TMP_GITTER_CLIENT_ACCESS_EVENTS.d__agent:type, EEASTWOOD_SCRATCH.TMP_GITTER_CLIENT_ACCESS_EVENTS.d__agent:version, EEASTWOOD_SCRATCH.TMP_GITTER_CLIENT_ACCESS_EVENTS.d__client_id, EEASTWOOD_SCRATCH.TMP_GITTER_CLIENT_ACCESS_EVENTS.d__client_key, EEASTWOOD_SCRATCH.TMP_GITTER_CLIENT_ACCESS_EVENTS.d__client_name, EEASTWOOD_SCRATCH.TMP_GITTER_CLIENT_ACCESS_EVENTS.d__env, EEASTWOOD_SCRATCH.TMP_GITTER_CLIENT_ACCESS_EVENTS.d__tag, EEASTWOOD_SCRATCH.TMP_GITTER_CLIENT_ACCESS_EVENTS.d__user_id, EEASTWOOD_SCRATCH.TMP_GITTER_CLIENT_ACCESS_EVENTS.t, EEASTWOOD_SCRATCH.TMP_GITTER_CLIENT_ACCESS_EVENTS.time_extracted)'] (Background on this error at: http://sqlalche.me/e/f405)
catalog.json
I just manually wrote in the properties
// ...
{
"table_name": "gitter_client_access_events",
"stream": "gitter_client_access_events",
"metadata": [
{
"breadcrumb": [],
"metadata": {
"database-name": "cube",
"row-count": 45309105,
"selected": true,
"replication-method": "LOG_BASED",
"custom-select-clause": "t,d"
}
}
],
"tap_stream_id": "cube-gitter_client_access_events",
"schema": {
"type": "object",
"properties": {
"_id": {"type": "string"},
"t": {"type": "string"},
"d": {
"type": "object",
"properties": {
"env": {"type": "string"},
"userId": {"type": "string"},
"clientId": {"type": "string"},
"clientName": {"type": "string"},
"clientKey": {"type": "string"},
"tag": {"type": "string"},
"agent:type": {"type": "string"},
"agent:family": {"type": "string"},
"agent:version": {"type": "string"},
"agent:device:family": {"type": "string"},
"agent:os:family": {"type": "string"},
"agent:os:version": {"type": "string"}
}
}
}
}
},
// ...
Stream example
{"type": "STATE", "value": {"currently_syncing": "cube-gitter_client_access_events"}}
{"type": "SCHEMA", "stream": "gitter_client_access_events", "schema": {"type": "object", "properties": {"_id": {"type": "string"}, "t": {"type": "string"}, "d": {"type": "object", "properties": {"env": {"type": "string"}, "userId": {"type": "string"}, "clientId": {"type": "string"}, "clientName": {"type": "string"}, "clientKey": {"type": "string"}, "tag": {"type": "string"}, "agent:type": {"type": "string"}, "agent:family": {"type": "string"}, "agent:version": {"type": "string"}, "agent:device:family": {"type": "string"}, "agent:os:family": {"type": "string"}, "agent:os:version": {"type": "string"}}}}}, "key_properties": ["_id"]}
{"type": "ACTIVATE_VERSION", "stream": "gitter_client_access_events", "version": 1541199424491}
{"type": "RECORD", "stream": "gitter_client_access_events", "record": {"_id": "65cc337e2a614127667b775da2e98886", "t": "2018-11-03T03:56:51.955000Z", "d": {"userId": "2fb22c5305f51d10b7eb0b8613fae5f2", "clientId": "698e727de99bf5b2fc3f610ee7538421", "clientName": "Web Client", "clientKey": "web-internal", "tag": "web-app", "agent:type": "desktop", "agent:family": "Safari", "agent:version": "11.0.1", "agent:device:family": "Other", "agent:os:family": "Mac OS X", "agent:os:version": "10.13.1", "env": "prod"}}, "version": 1541199424491, "time_extracted": "2018-11-02T22:57:04.841020Z"}
Edited by Eric Eastwood