Onfleet source plugin error while syncing to Snowflake

Hello All,

Does anyone have Onfleet as a source, using Onfleet (v1.0.0)? In attempting to sync to Snowflake, using Snowflake (v3.2.3), I receive an error, but a sync to SQLite, using SQLite (v2.4.9) is successful.

The Snowflake sync error in the log is:

ERR failed to write batch error="failed to put file into stage with last resource put file://C:\\Users\\ED29FE~1.WIN\\AppData\\Local\\Temp\\onfleet_admins.json.1159616259 @cq_plugin_stage auto_compress=true: 001003 (42000): SQL compilation error:
syntax error line 1 at position 26 unexpected '~'.
parse error line 1 at position 33 near '65'.
parse error line 1 at position 41 near '76'.
parse error line 1 at position 47 near '84'.
parse error line 1 at position 52 near '111'.
syntax error line 1 at position 66 unexpected '.'.
syntax error line 1 at position 71 unexpected '.1159616259'.
syntax error line 1 at position 83 unexpected '@cq_plugin_stage'." duration=68.2582 len=2 module=snowflake-src table=onfleet_admins

Is there a more recent Onfleet source plugin that may have addressed syncing to Snowflake, or is there something I need to add to my YAML files?

Hi Ed :wave:,

Would you mind sharing your CQ configuration with any sensitive information redacted?

I am encountering an OnFleet → Snowflake sync error. One potential problem is that the source and destination have incompatible plugin-sdk versions. Since OnFleet is a partner plugin, they may not be using the latest plugin SDK version.

To test this, we can try using older versions of the destination plugin. Could you try:

snowflake v2.0.3

to test against v2 of our plugin-sdk, or

snowflake v1.1.18

to test against v1 of our plugin-sdk?

Hi M,

Identical errors for v2.0.3 and v1.1.18.
Please see the attached file.

Thank you for trying! Would you mind sharing your CQ configuration with sensitive information redacted?

FYI, I’ve created a bug ticket as I think we need to sanitize the file URI differently on Windows-based systems.

$ cat cloudquery-config/onfleet.yaml
kind: source
spec:
  name: "onfleet"
  path: "onfleet/onfleet"
  version: "v1.0.0"
  tables: ["onfleet_admins", "onfleet_hubs", "onfleet_tasks", "onfleet_teams", "onfleet_workers"]
  destinations: ["snowflake"]
  spec:
    api_key: "2dxxxxxxxxxxxxxxxxxxxxxxxxxxxx3c"
    list_tasks_from: "2000-01-01T01:00:00Z"

$ cat cloudquery-config/snowflake.yml
kind: destination
spec:
  name: snowflake
  path: cloudquery/snowflake
  version: "v1.1.18"
  write_mode: "append"
  spec:
    connection_string: "user:pwd@host/DB/SCHEMA?warehouse=COMPUTE_WH"

$ cat cloudquery-config/sqlite.yml
kind: destination
spec:
  name: sqlite
  path: cloudquery/sqlite
  version: "v2.4.9"
  spec:
    connection_string: ./db.sql

M, I also had tried this on a Linux AWS EC2 instance. Same results.

Can you share the exact errors you are getting when you run the sync on the Linux instance?

My mistake. I had only tested v3.2.3 yesterday. 1.1.18 and 2.0.3 are successful. 3.2.3 displays the following on the console:

[ec2-user@ip-172-31-57-122 onfleet]$ ./cloudquery sync cloudquery-config/onfleet.yaml cloudquery-config/snowflake.yml
Loading spec(s) from cloudquery-config/onfleet.yaml, cloudquery-config/snowflake.yml
Downloading https://github.com/cloudquery/cloudquery/releases/download/plugins-destination-snowflake-v3.2.3/snowflake_linux_amd64.zip
Downloading 100% |█████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████| (10/10 MB, 45 MB/s)
Starting migration for: onfleet (v1.0.0) -> [snowflake (v3.2.3)]
Error: failed to sync v1 source onfleet: rpc error: code = Internal desc = failed to write: failed to flush writer: table onfleet_admins has multiple columns for _cq_source_name. migrate manually or consider using 'migrate_mode: forced'

These errors were in the log:

2023-09-27T18:20:41Z ERR finished call grpc.code=Internal grpc.component=server grpc.error="rpc error: code = Internal desc = failed to write: failed to flush writer: table onfleet_admins has multiple columns for _cq_source_name. migrate manually or consider using 'migrate_mode: forced'" grpc.method=Migrate grpc.method_type=unary grpc.service=proto.Destination grpc.start_time=2023-09-27T18:20:39Z grpc.time_ms=1514.277 module=cli peer.address=@ protocol=grpc
2023-09-27T18:20:41Z ERR exiting with error error="failed to sync v1 source onfleet: rpc error: code = Internal desc = failed to write: failed to flush writer: table onfleet_admins has multiple columns for _cq_source_name. migrate manually or consider using 'migrate_mode: forced'" module=cli

Hi @comic-sawfish, can you try with v3.2.5 of the Snowflake destination? We released a new version with a fix to this issue, see here.