时间戳

首页 > 解决方案 > 在 Snow Flake 中调用 Rest API

azure - 在 Snow Flake 中调用 Rest API

问题描述

如何在 Snowflake 中调用 rest Api 调用到笔记本，因为 API 调用会生成需要存储在 Snowflake 本身中的输出文件

标签： azuresnowflake-cloud-data-platformrest

解决方案

您不必直接从 Snowflake 调用 API。您可以通过连接到 Snowflake DB 直接从 python 笔记本加载文件：

SQL 代码：

-- Create destination table to store your API queries results data
create or replace table public.tmp_table
    (page_id int,id int,status varchar,provider_status varchar,ts_created timestamp);

-- create a new format for your csv files
create or replace file format my_new_format type = 'csv' field_delimiter = ';' field_optionally_enclosed_by = '"' skip_header = 1;

-- Put your local file to the Snowflake's temporary storage
put file:///Users/Admin/Downloads/your_csv_file_name.csv @~/staged;

-- Copying data from storage into table
copy into public.tmp_table from @~/staged/your_csv_file_name.csv.gz file_format = my_new_format ON_ERROR=CONTINUE;


select * from public.tmp_table;

-- Delete temporary data
remove @~/staged/tmp_table.csv.gz;

你可以用 python 做同样的事情： https ://docs.snowflake.com/en/user-guide/python-connector-example.html#loading-data

target_table = 'public.tmp_table'
filename = 'your_csv_file_name'
filepath = f'/home/Users/Admin/Downloads/{filename}.csv'
conn = snowflake.connector.connect(
    user=USER,
    password=PASSWORD,
    account=ACCOUNT,
    warehouse=WAREHOUSE,
    database=DATABASE,
    schema=SCHEMA
)
conn.cursor().execute(f'put file://{filepath} @~/staged;')

result = conn.cursor().execute(f'''
    COPY INTO {target_table}
    FROM @~/staged/{filename}.gz
    file_format = (format_name = 'my_new_format'
    FIELD_OPTIONALLY_ENCLOSED_BY = '"'
    ESCAPE_UNENCLOSED_FIELD = NONE) ON_ERROR=CONTINUE;
''')
conn.cursor().execute(f'REMOVE @~/staged/{filename}.gz;')

推荐阅读