python - 将表格导入雪花的 Python 工具

Question

我正在尝试将 csv 文件上传到 Snowflake，但看起来 COPY INTO 语法失败，向我显示回滚消息，创建数据阶段和表很好，我只能与 ODBC 连接，这部分也有效，什么是不工作是复制句子。

这是我的复制句子，它是唯一不起作用的句子：

sql = "COPY INTO \"PAYMENTS\".\"PAYMENTS_USER_RW\".\"STUDENT_MATH_MARK\" FROM @data_stage/ FILES = ('Student_marks.csv') FILE_FORMAT = (type = 'csv' field_delimiter = ',' skip_header = 1);"
execute_query(conn, sql)

这是我的代码：

import pyodbc

conn = pyodbc.connect('DSN=MYODBC;UID=MYUSER')

def execute_query(connection, query):
    cursor = connection.cursor()
    cursor.execute(query)
    cursor.close()


try:
    sql = 'use role {}'.format('APP_SNOWFLAKE_BDP_PAYMENT_RO')
    execute_query(conn, sql)

    sql = 'use database {}'.format('PAYMENTS')
    execute_query(conn, sql)

    sql = 'use warehouse {}'.format('BDP_PAYMENT_XSMALL_WH')
    execute_query(conn, sql)

    sql = 'use schema {}'.format('PAYMENTS_USER_RW ')
    execute_query(conn, sql)


    sql = 'drop table if exists "PAYMENTS"."PAYMENTS_USER_RW".student_math_mark'
    execute_query(conn, sql)


    sql = 'drop stage if exists data_stage'
    execute_query(conn, sql)

    sql = 'create stage "PAYMENTS"."PAYMENTS_USER_RW".data_stage file_format = (type = "csv" field_delimiter = "," skip_header = 1)'
    execute_query(conn, sql)

    csv_file = 'C:\\Users\\MYUSER\\Downloads\\Student_marks.csv'
    sql = "PUT file://" + csv_file + " @data_stage auto_compress=FALSE"
    execute_query(conn, sql)

    sql = 'CREATE OR REPLACE table "PAYMENTS"."PAYMENTS_USER_RW".student_math_mark(name varchar, mark double)'
    execute_query(conn, sql)

          

    sql = "COPY INTO \"PAYMENTS\".\"PAYMENTS_USER_RW\".\"STUDENT_MATH_MARK\" FROM @data_stage/ FILES = ('Student_marks.csv') FILE_FORMAT = (type = 'csv' field_delimiter = ',' skip_header = 1);"
    execute_query(conn, sql)
 
    sql = "ALTER WAREHOUSE BDP_PAYMENT_XSMALL_WH SUSPEND"
    execute_query(conn, sql)
    
    sql = 'select * from "PAYMENTS"."PAYMENTS_USER_RW"."STUDENT_MATH_MARK"'
    execute_query(conn, sql)
    cursor = conn.cursor()
    cursor.execute(sql)
    for c in cursor:
        print(c)

except Exception as e:
    print(e)

知道为什么 COPY 失败了吗？

问候

标签： pythonsqlcsvsnowflake-cloud-data-platform