search

datamill-co / target-snowflake

A Singer.io Target for Snowflake
MIT License
11 stars 25 forks source link

Field quoting issue #21

Open samplx opened 3 years ago

samplx commented 3 years ago

I am using target-snowflake along with meltano and a local tap.

I had a case where a string field value which ends in a backslash “\” (U+005C), the loader process fails. The error is reported against another field in the row having an invalid format. Simply removing the character from the source data allowed the loader to succeed.

example meltano output:

target-snowflake    | ERROR Exception writing records
target-snowflake    | Traceback (most recent call last):
target-snowflake    |   File "/project/drafthouse/.meltano/loaders/target-snowflake/venv/lib/python3.8/site-packages/target_snowflake/snowflake.py", line 150, in write_batch
target-snowflake    |     written_batches_details = self.write_batch_helper(cur,
target-snowflake    |   File "/project/drafthouse/.meltano/loaders/target-snowflake/venv/lib/python3.8/site-packages/target_postgres/sql_base.py", line 834, in write_batch_helper
target-snowflake    |     batch_rows_persisted = self.write_table_batch(
target-snowflake    |   File "/project/drafthouse/.meltano/loaders/target-snowflake/venv/lib/python3.8/site-packages/target_snowflake/snowflake.py", line 538, in write_table_batch
target-snowflake    |     self.persist_csv_rows(cur,
target-snowflake    |   File "/project/drafthouse/.meltano/loaders/target-snowflake/venv/lib/python3.8/site-packages/target_snowflake/snowflake.py", line 479, in persist_csv_rows
target-snowflake    |     cur.execute('''
target-snowflake    |   File "/project/drafthouse/.meltano/loaders/target-snowflake/venv/lib/python3.8/site-packages/target_snowflake/connection.py", line 20, in execute
target-snowflake    |     super(MillisLoggingCursor, self).execute(command, **kwargs)
target-snowflake    |   File "/project/drafthouse/.meltano/loaders/target-snowflake/venv/lib/python3.8/site-packages/snowflake/connector/cursor.py", line 601, in execute
target-snowflake    |     Error.errorhandler_wrapper(self.connection, self,
target-snowflake    |   File "/project/drafthouse/.meltano/loaders/target-snowflake/venv/lib/python3.8/site-packages/snowflake/connector/errors.py", line 97, in errorhandler_wrapper
target-snowflake    |     cursor.errorhandler(connection, cursor, errorclass, errorvalue)
target-snowflake    |   File "/project/drafthouse/.meltano/loaders/target-snowflake/venv/lib/python3.8/site-packages/snowflake/connector/errors.py", line 68, in default_errorhandler
target-snowflake    |     raise errorclass(
target-snowflake    | snowflake.connector.errors.ProgrammingError: 100038 (22018): Numeric value '2021-01-16 09:03:43.1983+00:00' is not recognized
target-snowflake    |   File '@TMP_DD0B756A_F0E0_400A_9FAE_3DA84BE69210/159158ca_754b_43d0_b3cd_64787eb14ed7.gz', line 25930, character 96
target-snowflake    |   Row 25930, column "TMP_DD0B756A_F0E0_400A_9FAE_3DA84BE69210"["ID":2]
target-snowflake    |   If you would like to continue loading when an error is encountered, use other values such as 'SKIP_FILE' or 'CONTINUE' for the ON_ERROR option. For more information on loading options, please run 'info loading_data' in a SQL client.
target-snowflake    | CRITICAL ('Exception writing records', 100038 (22018): Numeric value '2021-01-16 09:03:43.1983+00:00' is not recognized
target-snowflake    |   File '@TMP_DD0B756A_F0E0_400A_9FAE_3DA84BE69210/159158ca_754b_43d0_b3cd_64787eb14ed7.gz', line 25930, character 96
target-snowflake    |   Row 25930, column "TMP_DD0B756A_F0E0_400A_9FAE_3DA84BE69210"["ID":2]
target-snowflake    |   If you would like to continue loading when an error is encountered, use other values such as 'SKIP_FILE' or 'CONTINUE' for the ON_ERROR option. For more information on loading options, please run 'info loading_data' in a SQL client.)
target-snowflake    | INFO MillisLoggingCursor: 43 millis spent executing: ROLLBACK
target-snowflake    | Traceback (most recent call last):
target-snowflake    |   File "/project/drafthouse/.meltano/loaders/target-snowflake/venv/lib/python3.8/site-packages/target_snowflake/snowflake.py", line 150, in write_batch
target-snowflake    |     written_batches_details = self.write_batch_helper(cur,
target-snowflake    |   File "/project/drafthouse/.meltano/loaders/target-snowflake/venv/lib/python3.8/site-packages/target_postgres/sql_base.py", line 834, in write_batch_helper
target-snowflake    |     batch_rows_persisted = self.write_table_batch(
target-snowflake    |   File "/project/drafthouse/.meltano/loaders/target-snowflake/venv/lib/python3.8/site-packages/target_snowflake/snowflake.py", line 538, in write_table_batch
target-snowflake    |     self.persist_csv_rows(cur,
target-snowflake    |   File "/project/drafthouse/.meltano/loaders/target-snowflake/venv/lib/python3.8/site-packages/target_snowflake/snowflake.py", line 479, in persist_csv_rows
target-snowflake    |     cur.execute('''
target-snowflake    |   File "/project/drafthouse/.meltano/loaders/target-snowflake/venv/lib/python3.8/site-packages/target_snowflake/connection.py", line 20, in execute
target-snowflake    |     super(MillisLoggingCursor, self).execute(command, **kwargs)
target-snowflake    |   File "/project/drafthouse/.meltano/loaders/target-snowflake/venv/lib/python3.8/site-packages/snowflake/connector/cursor.py", line 601, in execute
target-snowflake    |     Error.errorhandler_wrapper(self.connection, self,
target-snowflake    |   File "/project/drafthouse/.meltano/loaders/target-snowflake/venv/lib/python3.8/site-packages/snowflake/connector/errors.py", line 97, in errorhandler_wrapper
target-snowflake    |     cursor.errorhandler(connection, cursor, errorclass, errorvalue)
target-snowflake    |   File "/project/drafthouse/.meltano/loaders/target-snowflake/venv/lib/python3.8/site-packages/snowflake/connector/errors.py", line 68, in default_errorhandler
target-snowflake    |     raise errorclass(
target-snowflake    | snowflake.connector.errors.ProgrammingError: 100038 (22018): Numeric value '2021-01-16 09:03:43.1983+00:00' is not recognized
target-snowflake    |   File '@TMP_DD0B756A_F0E0_400A_9FAE_3DA84BE69210/159158ca_754b_43d0_b3cd_64787eb14ed7.gz', line 25930, character 96
target-snowflake    |   Row 25930, column "TMP_DD0B756A_F0E0_400A_9FAE_3DA84BE69210"["ID":2]
target-snowflake    |   If you would like to continue loading when an error is encountered, use other values such as 'SKIP_FILE' or 'CONTINUE' for the ON_ERROR option. For more information on loading options, please run 'info loading_data' in a SQL client.
target-snowflake    | 
target-snowflake    | During handling of the above exception, another exception occurred:
target-snowflake    | 
target-snowflake    | Traceback (most recent call last):
target-snowflake    |   File "/project/drafthouse/.meltano/loaders/target-snowflake/venv/bin/target-snowflake", line 8, in <module>
target-snowflake    |     sys.exit(cli())
target-snowflake    |   File "/project/drafthouse/.meltano/loaders/target-snowflake/venv/lib/python3.8/site-packages/target_snowflake/__init__.py", line 57, in cli
target-snowflake    |     main(args.config)
target-snowflake    |   File "/project/drafthouse/.meltano/loaders/target-snowflake/venv/lib/python3.8/site-packages/target_snowflake/__init__.py", line 51, in main
target-snowflake    |     target_tools.main(target)
target-snowflake    |   File "/project/drafthouse/.meltano/loaders/target-snowflake/venv/lib/python3.8/site-packages/target_postgres/target_tools.py", line 28, in main
target-snowflake    |     stream_to_target(input_stream, target, config=config)
target-snowflake    |   File "/project/drafthouse/.meltano/loaders/target-snowflake/venv/lib/python3.8/site-packages/target_postgres/target_tools.py", line 77, in stream_to_target
target-snowflake    |     raise e
target-snowflake    |   File "/project/drafthouse/.meltano/loaders/target-snowflake/venv/lib/python3.8/site-packages/target_postgres/target_tools.py", line 70, in stream_to_target
target-snowflake    |     state_tracker.flush_streams(force=True)
target-snowflake    |   File "/project/drafthouse/.meltano/loaders/target-snowflake/venv/lib/python3.8/site-packages/target_postgres/stream_tracker.py", line 47, in flush_streams
target-snowflake    |     self._write_batch_and_update_watermarks(stream)
target-snowflake    |   File "/project/drafthouse/.meltano/loaders/target-snowflake/venv/lib/python3.8/site-packages/target_postgres/stream_tracker.py", line 67, in _write_batch_and_update_watermarks
target-snowflake    |     self.target.write_batch(stream_buffer)
target-snowflake    |   File "/project/drafthouse/.meltano/loaders/target-snowflake/venv/lib/python3.8/site-packages/target_snowflake/snowflake.py", line 164, in write_batch
target-snowflake    |     raise SnowflakeError(message, ex)
target-snowflake    | target_snowflake.exceptions.SnowflakeError: ('Exception writing records', 100038 (22018): Numeric value '2021-01-16 09:03:43.1983+00:00' is not recognized
target-snowflake    |   File '@TMP_DD0B756A_F0E0_400A_9FAE_3DA84BE69210/159158ca_754b_43d0_b3cd_64787eb14ed7.gz', line 25930, character 96
target-snowflake    |   Row 25930, column "TMP_DD0B756A_F0E0_400A_9FAE_3DA84BE69210"["ID":2]
target-snowflake    |   If you would like to continue loading when an error is encountered, use other values such as 'SKIP_FILE' or 'CONTINUE' for the ON_ERROR option. For more information on loading options, please run 'info loading_data' in a SQL client.)
meltano             | Loading failed (1):   If you would like to continue loading when an error is encountered, use other values such as 'SKIP_FILE' or 'CONTINUE' for the ON_ERROR option. For more information on loading options, please run 'info loading_data' in a SQL client.)
meltano             | ELT could not be completed: Loader failed
KBorders01 commented 2 years ago

I ran into this as well for a string that didn't otherwise need to be quoted ending in a backslash and absorbing the next columns value, leading the columns that followed to be incorrect.