从Snowflake读取大数据并使用python写入csv文件

Question

我必须使用python连接器在Snowflake中读取一个巨大的表（10M行），并将其写入csv文件

我在snowfalke文档中读到了fetchmany

fetchmany([size=cursor.arraysize])
Purpose
Fetches the next rows of a query result set and returns a list of sequences/dict. An empty sequence is returned when no more rows are available.

我如何使用它并将数据块写入csv文件，直到所有记录都被完全写入

Answer 1

最好是将数据写入stage，除非您实际上不必为此使用python

步骤是

一,

COPY INTO @~/stage_data
FROM (
(SELECT column_a, column_b, column_c 
FROM table_one
WHERE column_b IN (SELECT column_ FROM table_two )
)
file_format = (TYPE=CSV compression='gzip') single=true max_file_size=4900000000;

二,

get @~/data file:///~/;

注意：上面的示例使用user stage，但您可以将其更改为您喜欢的阶段类型

Answer 2

使用fetch\u pandas\u all（）并将数据加载到具有

`

import pandas as pd
import snowflake.connector
ctx = snowflake.connector.connect(
<connection details> )
cs = ctx.cursor()
query='select * from foo'
cs.execute(query)
df = cs.fetch_pandas_all()
df.to_csv('output.csv.gz',
  sep='|',
  header=True,
  index=False,
  quoting=csv.QUOTE_ALL,
  compression='gzip',
  quotechar='"',
  doublequote=True,
  line_terminator='\n')

`

Answer 3

注意：我在另一个导入的文件中提供了我的连接详细信息。我从数据库testdb和表store\u sales中选择了2列

import snowflake.connector
from connections import conn
from snowflake.connector import DictCursor
import csv

cur = conn.cursor(DictCursor)
try:
    sql = "use testdb"
    cur.execute(sql)
    sql = "select Branch_id , city from store_sales"
    cur.execute(sql)
    with open('file.csv', 'w+', newline="") as f:
        csv_writer = csv.writer(f , delimiter=',')
        for rec in cur:
            csv_writer.writerow([rec['BRANCH_ID'],rec['CITY']])       
finally:
    cur.close()