在Hive中添加逗号分隔的表格 [英] Adding a comma separated table to Hive

问题描述

我有一个非常基本的问题，那就是:如何向Hive中添加一个非常简单的表.我的表保存在一个文本文件(.txt)中，该文件保存在HDFS中.我试图在Hive中创建一个指向该文件的外部表，但是当我运行SQL查询(从table_name中选择*)时，没有任何输出. 这是示例代码:

I have a very basic question which is: How can I add a very simple table to Hive. My table is saved in a text file (.txt) which is saved in HDFS. I have tried to create an external table in Hive which points out this file but when I run an SQL query (select * from table_name) I don't get any output. Here is an example code:

create external table Data (
    dummy INT,
    account_number INT, 
    balance INT, 
    firstname STRING, 
    lastname STRING, 
    age INT, 
    gender CHAR(1), 
    address STRING, 
    employer STRING, 
    email STRING,
    city STRING, 
    state CHAR(2)
)
LOCATION 'hdfs:///KibTEst/Data.txt';

KibTEst/Data.txt是HDFS中文本文件的路径.

KibTEst/Data.txt is the path of the text file in HDFS.

表中的行用回车符分隔，列用逗号分隔.

The rows in the table are seperated by carriage return, and the columns are seperated by commas.

感谢您的帮助！

推荐答案

1. 您只需要创建一个指向文件的外部表 在hdfs中的位置，并具有如下分隔符属性:

1. You just need to create an external table pointing to your file location in hdfs and with delimiter properties as below:

create external table Data (
    dummy INT,
    account_number INT, 
    balance INT, 
    firstname STRING, 
    lastname STRING, 
    age INT, 
    gender CHAR(1), 
    address STRING, 
    employer STRING, 
    email STRING,
    city STRING, 
    state CHAR(2)
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ',' 
LINES TERMINATED BY '\n'
LOCATION 'hdfs:///KibTEst/Data.txt';

您需要运行select查询(因为文件已经在HDFS中，并且当在create语句中指定位置时，外部表直接从中获取数据).因此，您可以使用以下选择语句进行测试:

You need to run select query(because file is already in HDFS and external table directly fetches data from it when location is specified in create statement). So you test using below select statement:

SELECT * FROM Data;

这篇关于在Hive中添加逗号分隔的表格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！