Я создал таблицу
CREATE TABLE "VALIDATION_RULES_RESULTS" (
id INT AUTOINCREMENT,
created_on TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
mongo_data_ingestion_id STRING,
table_name STRING,
col_name STRING,
rule_name STRING,
success STRING,
element_count INT,
missing_count INT,
failed_records_indexes VARIANT,
failed_records_values VARIANT
);
ниже приведен мой скрипт на Python
insert_statement = f"""
INSERT INTO "VALIDATION_RULES_RESULTS" (
mongo_data_ingestion_id,
table_name,
col_name,
rule_name,
success,
element_count,
missing_count,
failed_records_indexes,
failed_records_values
) select
mongo_data_ingestion_id,
table_name,
col_name,
rule_name,
success,
element_count,
missing_count,
parse_json(failed_records_indexes),
parse_json(failed_records_values)
from VALUES (
%(mongo_data_ingestion_id)s,
%(table_name)s,
%(col_name)s,
%(rule_name)s,
%(success)s,
%(element_count)s,
%(missing_count)s,
%(failed_records_indexes)s,
%(failed_records_values)s
)
"""
# Prepare the data for executemany
rows_to_insert = []
for rcd in snowflake_validation_rule_row:
row = {
'mongo_data_ingestion_id': rcd['mongo_data_ingestion_id'],
'table_name': rcd['table_name'],
'col_name': rcd['col_name'],
'rule_name': rcd['rule_name'],
'success': str(rcd['success']).lower(),
'element_count': rcd['element_count'],
'missing_count': rcd['missing_count'],
'failed_records_indexes': json.dumps(rcd['failed_records_indexes'], default=str),
'failed_records_values': json.dumps(rcd['failed_records_values'], default=str)
}
rows_to_insert.append(row)
snoflake_conn.cursor().executemany(
insert_statement,
rows_to_insert
)
выходит ошибка
20:44:13.63 !!! snowflake.connector.errors.ProgrammingError: 000904 (42000): SQL compilation error: error line 12 at position 8
20:44:13.63 !!! invalid identifier 'MONGO_DATA_INGESTION_ID'
20:44:13.63 !!! When calling: snoflake_conn.cursor().executemany(
20:44:13.63 insert_statement,
20:44:13.63 rows_to_insert
20:44:13.63 )
20:44:13.63 !!! Call ended by exception
Подробнее здесь: https://stackoverflow.com/questions/790 ... -to-insert
