我不熟悉sql/redshift,正在寻找合并到表的最佳方式。在
我在redshift中有一个大的(ish)表(大约2kcols×50k行)。该表具有datetime sortkey。我在python/pandas数据帧中迭代地将sortkey和1个其他列拉入执行一些(相当复杂)生成新列的操作。然后,我将这个pandas数据帧转换成另一个redshift表,我想将它与原始表合并,这样只会追加新的列(尽管更新整个内容并不重要)。两个表都有相同的sortkey,这应该很简单,对吧?基本上只是添加一些新的列?(请原谅我的天真)
表1
datetime, rainfall, windspeed, cloudcover
2000-01-01,5,5,5
2000-01-02,7,5,5
2000-01-03,1,5,5
2000-01-04,0,5,5
潘达斯DF1
^{pr2}$以下是我希望实现的目标:
datetime, rainfall, windspeed, cloudcover,rainfall_movingAverage, other_calculation
2000-01-01,5,5,5,5,NaN
2000-01-02,7,5,5,6,4.56
2000-01-03,1,5,5,4.3,7.53
2000-01-04,0,5,5,3.75,3.55
在熊猫身上,这一点非常简单,可以通过多种方式实现,其中一种方法是:
result = pd.concat([table1, pandasdf1], axis=1, sort=True)
然而,数据帧的大小正在使pandas崩溃,数据将变得比现在大得多,所以我不得不将其迁移到redshift。我尝试使用以下命令进行合并:
SELECT * FROM table1
FULL OUTER JOIN pandasdf ON (table1.datetime = pandasdf.datetime)
这似乎可以工作(至少不会崩溃或返回错误),但原始表没有更新。我似乎找不到更新原始表的附加语法。请注意,我正在使用pythonsql引擎与redshift交互
import psycopg2
SQL="""
SELECT * FROM table1
FULL OUTER JOIN pandasdf ON (table1.datetime = pandasdf.datetime)
"""
def merge_redshift_tables(SQL):
"""merge the left and right tables"""
success=False
try:
conn=None
"""Establish a connection to redshift"""
conn=psycopg2.connect(dbname= 'mydb', host='myIP', port= 'myport', user= 'myusername', password= 'mypassword')
"""make a cursor object"""
cur = conn.cursor()
cur.execute(SQL)
success=True
except psycopg2.Error as e:
print(e)
finally:
if conn is not None:
conn.close()
return success
如果有人能帮我完成这一步,那将是一个伟大的第一步。但是,我不确定这是否是对~2000列中的每一列进行这种操作的最佳方法,因此如果有人能分享一些关于最佳实践的智慧,我也会非常感激。我粗略地计划将工作分布在多个并行工作的计算节点上,但是这取决于redshifts平滑地合并所有这些新列的能力(我知道这可能是个问题)。欢迎就这方面的最佳做法提出任何建议。在
非常感谢
#####编辑以下各项似乎运行正常,表明已成功创建包含所需列的新表:
SELECT t1.*, t2.new_col
INTO TABLE combined FROM table1 t1
LEFT JOIN pandasdf1 t2 ON t1.datetime = t2.datetime;
但是,当我查询时,它返回一个错误,提示没有新表:
def get_col(table, col='*'):
"""Gets all data from a column from a table"""
coldata=None
try:
conn=None
"""Establish a connection to redshift"""
conn=psycopg2.connect(dbname= 'mydb', host='myIP', port= 'myport', user= 'myusername', password= 'mypassword')
coldata = pd.read_sql("select {} FROM {}".format(col, table), conn).set_index('gmt_reportedtime').dropna()
except psycopg2.Error as e:
print(e)
finally:
if conn is not None:
conn.close()
return coldata
check = get_col('combined')
退货:
pandas.io.sql.DatabaseError: Execution failed on sql 'select * FROM combined': relation "combined" does not exist
编辑
我现在修好了!使用python语句需要提交更改:
conn.commit()
您可以使用以下命令“原始表未更新”:
SQL中的
SELECT
命令返回数据。它不更新数据。在如果要创建新的组合表,可以使用:
^{pr2}$参见:SELECT INTO - Amazon Redshift
您需要使用一个新表,因为“original”
table1
只定义为具有原始的4列。虽然您可以修改表,添加列,然后运行UPDATE
命令,但是创建一个新的表是一个更好的主意(对于Amazon Redshift来说也更有效)。在相关问题 更多 >
编程相关推荐