Как записать данные в Redshift, что является результатом данных, созданных в Python?

Ответ 1

Вы можете использовать to_sql для to_sql данных в базу данных Redshift. Я смог сделать это, используя соединение с моей базой данных через механизм SQLAlchemy. Просто не забудьте установить index = False в вызове to_sql. Таблица будет создана, если она не существует, и вы можете указать, хотите ли вы позвонить, чтобы заменить таблицу, добавить в таблицу или потерпеть неудачу, если таблица уже существует.

from sqlalchemy import create_engine
import pandas as pd

conn = create_engine('postgresql://username:[email protected]:5439/yourdatabase')

df = pd.DataFrame([{'A': 'foo', 'B': 'green', 'C': 11},{'A':'bar', 'B':'blue', 'C': 20}])

df.to_sql('your_table', conn, index=False, if_exists='replace')

Обратите внимание, что вам может потребоваться pip install psycopg2 для подключения к Redshift через SQLAlchemy.

Документация to_sql

Ответ 2

import pandas_redshift as pr

pr.connect_to_redshift(dbname = <dbname>,
                        host = <host>,
                        port = <port>,
                        user = <user>,
                        password = <password>)

pr.connect_to_s3(aws_access_key_id = <aws_access_key_id>,
                aws_secret_access_key = <aws_secret_access_key>,
                bucket = <bucket>,
                subdirectory = <subdirectory>)

# Write the DataFrame to S3 and then to redshift
pr.pandas_to_redshift(data_frame = data_frame,
                        redshift_table_name = 'gawronski.nba_shots_log')

Подробности: https://github.com/agawronski/pandas_redshift

Ответ 3

Предполагая, что у вас есть доступ к S3, этот подход должен работать:

Шаг 1: Напишите DataFrame как csv-S3 (для этого я использую AWS SDK boto3)
Шаг 2. Вы знаете столбцы, типы данных и ключ/индекс для своей таблицы Redshift из вашего DataFrame, поэтому вы должны иметь возможность генерировать create table script и нажимать на Redshift для создания пустой таблицы
Шаг 3: Отправьте команду copy из вашей среды Python в Redshift, чтобы скопировать данные из S3 в пустую таблицу, созданную на шаге 2

Работает как шарм каждый раз.

Шаг 4: Прежде чем ваши пользователи облачного хранилища начнут кричать, вы удалите csv из S3

Если вы видите, что делаете это несколько раз, обертывание всех четырех шагов в функции сохраняет порядок.

Ответ 4

Я пытался использовать pandas df.to_sql() но это было очень медленно. Мне понадобилось более 10 минут, чтобы вставить 50 строк. Смотрите этот открытый вопрос (на момент написания)

Я попытался использовать odo из odo экосистемы (согласно рекомендациям в обсуждении проблемы), но столкнулся с odo ProgrammingError которую я не удосужился исследовать.

Наконец то, что сработало:

import psycopg2

# Fill in the blanks for the conn object
conn = psycopg2.connect(user = 'user',
                              password = 'password',
                              host = 'host',
                              dbname = 'db',
                              port = 666)
cursor = conn.cursor()

args_str = b','.join(cursor.mogrify("(%s,%s,...)", x) for x in tuple(map(tuple,np_data)))
cursor.execute("insert into table (a,b,...) VALUES "+args_str.decode("utf-8"))

cursor.close()
conn.commit()
conn.close()

Да, старый psycopg2. Это для простого массива, но преобразование из df в ndarray не должно быть слишком сложным. Это дало мне около 3 тысяч строк в минуту.

Однако самое быстрое решение в соответствии с рекомендациями других товарищей по команде заключается в использовании команды COPY после выгрузки кадра данных в качестве TSV/CSV в кластер S3 и последующего копирования. Вы должны разобраться в этом, если копируете действительно огромные наборы данных. (Я буду обновлять здесь, если и когда я попробую это)

Ответ 5

Для этой беседы Postgres = RedShift У вас есть два варианта:

Вариант 1:

Из Pandas: http://pandas.pydata.org/pandas-docs/stable/io.html#io-sql

Модуль pandas.io.sql предоставляет набор оберток запросов для облегчения извлечения данных и уменьшения зависимости от API-интерфейса, специфичного для БД. Абстракция базы данных обеспечивается SQLAlchemy, если она установлена. Кроме того, вам понадобится библиотека драйверов для вашей базы данных. Примерами таких драйверов являются psycopg2 для PostgreSQL или pymysql для MySQL.

Написание DataFrames

Предполагая, что следующие данные в данных DataFrame, мы можем вставить их в базу данных, используя to_sql().

id  Date    Col_1   Col_2   Col_3
26  2012-10-18  X   25.7    True
42  2012-10-19  Y   -12.4   False
63  2012-10-20  Z   5.73    True

In [437]: data.to_sql('data', engine)

В некоторых базах данных запись больших DataFrames может привести к ошибкам из-за превышения ограничений размера пакета. Этого можно избежать, установив параметр chunksize при вызове to_sql. Например, следующее записывает данные в базу данных партиями по 1000 строк за раз:

In [438]: data.to_sql('data_chunked', engine, chunksize=1000)

Вариант 2

Или вы можете просто сделать свой собственный Если у вас есть dataframe, называемый data, просто перебирайте его, используя iterrows:

for row in data.iterrows():

затем добавьте каждую строку в вашу базу данных. Я бы использовал копию вместо вставки для каждой строки, так как она будет намного быстрее.

http://initd.org/psycopg/docs/usage.html#using-copy-to-and-copy-from

Ответ 6

Если я хочу записать в подпапку моей базы данных, как это сделать?

Спасибо

Ответ 7

Раньше я полагался на to_sql() панд, но она слишком медленная. Я недавно переключился на следующее:

import pandas as pd
import s3fs # great module which allows you to read/write to s3 easily
import sqlalchemy

df = pd.DataFrame([{'A': 'foo', 'B': 'green', 'C': 11},{'A':'bar', 'B':'blue', 'C': 20}])

s3 = s3fs.S3FileSystem(anon=False)
filename = 'my_s3_bucket_name/file.csv'
with s3.open(filename, 'w') as f:
    df.to_csv(f, index=False, header=False)

con = sqlalchemy.create_engine('postgresql://username:[email protected]:5439/yourdatabase')
# make sure the schema for mytable exists

# if you need to delete the table but not the schema leave DELETE mytable
# if you want to only append, I think just removing the DELETE mytable would work

con.execute("""
    DELETE mytable;
    COPY mytable
    from 's3://%s'
    iam_role 'arn:aws:iam::xxxx:role/role_name'
    csv;""" % filename)

роль должна разрешить доступ с красным смещением к S3, подробнее см. здесь

Я обнаружил, что для файла размером 300 КБ (12000x2), это занимает 4 секунды по сравнению с 8 минутами, которые я получал с to_sql() функции pandas to_sql()