如何在python中读取csv文件并返回外部表查询

Question

我正在尝试读取csv文件，并通过数据帧创建一个外部表查询。请帮我怎样才能达到我的目标？

示例：

Sppose我有这样的df-

df = pd.DataFrame({'A': [1,2,3], 'B': [True, False, False], 'C': ['a', 'b', 'c']})
print(df.dtypes)
A     int64
B      bool
C    object
dtype: object

我必须根据数据帧提供的信息创建外部表

CREATE EXTERNAL TABLE schema_name.table_name
(
A INT,
B VARCHAR(100),
C VARCHAR(100)
) ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
WITH SERDEPROPERTIES 
(
'separatorChar' = ','
)
LOCATION 'location'
TABLE PROPERTIES ('skip.header.line.count'='1') ;

转换应该是这样的-

int64  - INT,
float64 - FLOAT,
object - VARCHAR(100),
bool - VARCHAR(10),
date - TIMESTAMP

请帮助我如何创建外部表？

Answer 1

SQL query是普通字符串，因此您可以将其格式化为任何其他字符串。

您可以使用for-循环和df.dtypes.items()来获得name和dtype，并将其转换为预期的行A INT、B VARCHAR(100)和C VARCHAR(100)。

你可以使用dictionary将其转换为

convert = {
"int64": "INT",
"float64": "FLOAT",
"object": "VARCHAR(100)",
"bool": "VARCHAR(10)",
"date": "TIMESTAMP",    
}    

稍后，您可以将join与",n"一起使用，以便在除一行之外的所有行中添加逗号。

最后，您可以使用f-string或.format()将其放入字符串CREATE ...中

import pandas as pd
df = pd.DataFrame({'A': [1,2,3], 'B': [True, False, False], 'C': ['a', 'b', 'c']})
#print(df.dtypes)
convert = {
"int64": "INT",
"float64": "FLOAT",
"object": "VARCHAR(100)",
"bool": "VARCHAR(10)",
"date": "TIMESTAMP",    
}    

all_lines = []    
for name, dtype in df.dtypes.items():
dtype = str(dtype)
line  = f'{name} {convert[dtype]}'
#print(dtype, "=>", line)
all_lines.append( line )

text = ",n".join(all_lines)
print(f"""CREATE EXTERNAL TABLE schema_name.table_name
(
{text}
) ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
WITH SERDEPROPERTIES 
(
'separatorChar' = ','
)
LOCATION 'location'
TABLE PROPERTIES ('skip.header.line.count'='1') ;""")

结果：

CREATE EXTERNAL TABLE schema_name.table_name
(
A INT,
B VARCHAR(10),
C VARCHAR(100)
) ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
WITH SERDEPROPERTIES 
(
'separatorChar' = ','
)
LOCATION 'location'
TABLE PROPERTIES ('skip.header.line.count'='1') ;

Answer 2

不确定您的目标是什么。您是否试图从现有的pandas数据帧生成sql查询？看起来像是一个straigthforward任务——迭代数据帧列，生成查询的相应部分，并使用字符串格式生成结果。

考虑以下可能的实现：

import pandas as pd
# pandas types to sql types mapping
TYPE_MAP = {
'object': 'VARCHAR({})',
'int64': 'INT',
'float64': 'FLOAT',
'bool': 'VARCHAR({})',
'datetime64': 'TIMESTAMP',
'timedelta[ns]': 'NotImplemented',
'category': 'NotImplemented'}
def create_query(data, data_config):
query_template = """CREATE EXTERNAL TABLE schema_name.table_name
(
{}
) parameters
parameters"""
query_columns = list()
for col in data:
col_type = TYPE_MAP[str(data[col].dtype)]
if col_type.startswith('VARCHAR'):
# compute max_length from data
# col_type = col_type.format(data[col].astype('str').str.len().max())
# or use pre-defined values
col_type = col_type.format(data_config['varchar_length'][col])
query_columns.append(f"{col} {col_type}")
return query_template.format(',n'.join(query_columns))

if __name__ == '__main__':
df = pd.DataFrame({'A': [1, 2, 3], 'B': [True, False, False], 'C': ['a', 'b', 'c']})
cfg = {'varchar_length': {'B': 90, 'C': 110}}
query = create_query(df, cfg)
print(query)

结果是：

CREATE EXTERNAL TABLE schema_name.table_name
(
A INT,
B VARCHAR(90),
C VARCHAR(110)
) parameters
parameters