从文本文件导入数据到hive表中
1.数据保存为CSV格式，不能带标题行，以逗号分隔，用tr命令将逗号替换成hive默认的 01分隔
tr ',' ' 01' < /home/zengsiwei362/zhoulixin54520150730.csv > /home/zengsiwei362/zhoulixin54520150730.txt
2.建一张与目标表一样的临时表，可以是分区表也可以不是分区表，导入的语句不同而已，但是文件格式一定是textfile
3.导入数据到tmp表，再从tmp表加载到正式表，文件可以放在本地，也可以放到hdfs，本地：load data local inpath ... hdfs:去掉local
另外”overwrite into “和”into“的区别没验证过
hive -e " use pad_hdp;
load data local inpath '/home/zengsiwei362/zhoulixin54520150730.txt' overwrite into table pad_hdp.DWY2_CUST_VEHICLE_import_tmp ;
--load data local inpath '/home/zengsiwei362/zhoulixin54520150730.txt' overwrite into table pad_hdp.DWY2_CUST_VEHICLE_import_tmp partition(op_day='20150707');
set mapred.job.queue.name=queue02;
INSERT overwrite TABLE pad_hdp.DWY2_CUST_VEHICLE PARTITION (OP_DAY = '20150707')
SELECT *
FROM pad_hdp.DWY2_CUST_VEHICLE_import_tmp ;"
建表语句：
USE PAD_HDP;
DROP TABLE DWY2_CUST_VEHICLE_import_tmp;
CREATE TABLE DWY2_CUST_VEHICLE_import_tmp(
list_no string comment '',
vehicle_no string comment '',
tcims_cust_id string comment '',
asset_id double comment '',
contact_name string comment '',
contact_email string comment '',
zip_code string comment '',
address string comment '',
remark string comment '',
driver_license_no string comment '',
driver_license_fst_issue_date string comment '',
drive_vehicle_type_code string comment '',
brand_type_code string comment '',
usage_attribute string comment '',
usage_code string comment '',
attribute_code string comment '',
vehicle_type string comment '',
vehicle_type_code string comment '',
vehicle_body_color string comment '',
automodel_name string comment '',
brand_type_first_sale_date string comment '',
seat_number double comment '',
ton_number double comment '',
exhaust double comment '',
foreign_vehicle_no string comment '',
engine_number string comment '',
vehicle_frame string comment '',
dev_code string comment '',
refix_desc string comment '',
vehicle_value double comment '',
vehicle_remark string comment '',
vehicle_owner string comment '',
insured_person_name string comment '',
insured_person_address string comment '',
main_driver_no string comment '',
main_driver_dob string comment '',
main_driver_sex string comment '',
main_driver_name string comment '',
last_policy_no string comment '',
last_year_apply_company string comment '',
policy_effective_date string comment '',
violation_ratio double comment '',
claim_ratio double comment '',
violation_premium_change double comment '',
drive_area_code string comment '',
policy_no string comment '',
department_chinese_name string comment '',
first_register_date string comment '',
vehicle_age string comment '',
policy_end_date string comment '',
secondary_org string comment '',
third_org string comment '',
city string comment '',
area_info string comment '',
pa_apply_history string comment '',
pa_life_client string comment '',
cust_class string comment '',
cust_type string comment '',
sale_result_class string comment '',
shield_flag string comment '',
list_type_code string comment '',
source_channel_code string comment '',
tcims_batch_id string comment '',
contact_type string comment '',
factory_logo string comment '',
vehicle_series string comment '',
vehicle_class_code string comment '',
insurance_type_flag string comment '',
assignee string comment '',
is_address_valid string comment '',
is_agency_phone string comment '',
risk_times double comment '',
src_type_code string comment '',
city_code string comment '',
sex_code string comment '',
channel_source_detail_cd string comment '',
list_priority double comment '',
updated_by string comment '',
created_by string comment '',
updated_date string comment '',
created_date string comment '',
prepaid_amount double comment '',
suspensive_amount double comment '',
all_tel string comment '',
list_type string comment '',
c51_phone_result string comment '',
c51_sale_stage string comment '',
c01_phone_result string comment '',
c01_sale_stage string comment '',
purchase_price double comment '',
vehicle_status string comment '',
vt_factory string comment '',
sql_sys_id string comment '',
offer_logo string comment '',
credit_card_logo string comment '',
double_segment_logo string comment '',
special_cut_logo string comment '',
repeat_cut_logo string comment '',
tel_no_valid_logo string comment '',
outer_customer_logo string comment '',
list_model_type string comment '',
applicant_party_no string comment '',
dissatisfy_logo string comment '',
contact_province string comment '',
contact_city string comment '',
contact_area string comment '',
contact_addr_detail string comment '',
pre_col_inventory1 string comment '',
pre_col_inventory2 string comment '',
pre_col_inventory3 string comment '',
pre_col_priority1 string comment '',
source_mark string comment '',
pre_col_priority3 string comment '',
premium double comment '',
biz_model string comment '',
dealer_code string comment '',
department_code string comment '',
fourth_org string comment '',
partner_code string comment '',
pre_col_compare1 string comment '',
pre_col_compare2 string comment '',
pre_col_compare3 string comment '',
valid_biz_batch string comment '',
department_name string comment '',
sale_result_valid string comment '',
is_contacted string comment '',
is_receipted double comment '',
is_quoted double comment '',
all_src_type string comment '',
business_mode string comment '',
c01_premium double comment '',
net_email2 string comment '',
net_email3 string comment '',
from_pa18 string comment '',
grant_grade string comment '',
cust_attribute_code double comment '',
cust_attribute_desc string comment '',
multi_veh_logo string comment '',
multi_veh_id string comment '',
salutation string comment '',
marital_status string comment '',
policy_end_date_day string comment '',
remark4 string comment '',
remark9 string comment '',
channel string comment '',
business string comment '',
last_underwriting_days_ahead string comment '',
last_con_count double comment '',
is_online_task string comment '',
wuche_flag string comment '',
obmgr_flag string comment '',
list_src_ctg_type string comment '',
all_columns string comment '',
underwrite_year_cnt string comment '',
lose_efficacy_day_cnt string comment '',
c01_premium_last double comment '',
is_different_month_c01_c51 string comment '',
is_cancel_last string comment '',
underwrite_type string comment '',
carcase_cnt_last string comment '',
before_underwrite_day_cnt_last int comment '',
list_rank_last string comment '',
cust_type_last string comment '',
call_cnt_last string comment '',
tmr_last string comment '',
last_year_call_total_time string comment '',
last_year_call_total_cnt string comment '',
payment_method_last string comment '',
ib_flag_last string comment '',
wx_flag_last string comment '',
is_only_wx_cust_last string comment '',
last_sale_result string comment ''
)COMMENT '' ROW FORMAT DELIMITED FIELDS TERMINATED BY ' 01' STORED AS textFILE;
最后
以上就是和谐小鸭子最近收集整理的关于从文本文件导入数据到hive表中的全部内容，更多相关从文本文件导入数据到hive表中内容请搜索靠谱客的其他文章。
发表评论 取消回复