从excel读取数据并插入到配置单元中

frebpwbc 于 2021-06-26 发布在 Hive

关注(0)|答案(2)|浏览(447)

我正在用Pandas从excel表格中读取数据。我需要使用pyspark将数据插入到配置单元中。

sparkConf = SparkConf().setAppName("App")
sc = SparkContext(conf = sparkConf)

sqlContext = HiveContext(sc)

excel_file = pd.ExcelFile("export_n_moreExportData10846.xls")
for sheet_name in excel_file.sheet_names:
try:
    df = pd.read_excel(excel_file, header=None, squeeze=True, sheet_name=sheet_name)
    for i, row in df.iterrows():
        if row.notnull().all():
            data = df.iloc[(i+1):].reset_index(drop=True)
                data.columns = list(df.iloc[i])
                break
        for c in data.columns:
        data[c] = pd.to_numeric(data[c], errors='ignore')
    print data #I need to insert this data into HIVE

except:
    continue

Hive pyspark pandas pyspark-sql

来源：https://stackoverflow.com/questions/48379886/read-data-from-excel-and-insert-into-hive

2条答案

按热度按时间

jfewjypa1#

如果列类型与spark兼容，则可以使用以下代码保存Dataframe：

tablename = 'your_table_name'
df_spark = sqlContext.createDataFrame(data)

# Remove spaces from your column names

columns_with_spaces = filter(lambda x:' ' in x,df.columns)
for column in columns_with_spaces:
     old_column = column
     new_column = column.replace(' ','_')
     df_spark =  df_spark.withColumnRenamed(old_column , new_column)

# Save to Hive

df_spark.write.mode('overwrite').saveAsTable(tableName)

赞(0）回复(0）举报 2021-06-26

yduiuuwa2#

您可以查看hadoopoffice库，它提供excel读/写功能，在主要的大数据平台（mr、hive、flink、spark…）上具有许多功能：https://github.com/zuinnote/hadoopoffice/wiki

赞(0）回复(0）举报 2021-06-26