Hive-table with multiple columns

step1:create a file with ctrl+A/'\001' as delimiter
[cloudera@quickstart ~]$ cat > file1
1^A2^A3
4^A5^A6
7^A8^A9
^C

step2:create a table with 3 cols
hive>create table tab2(x int,y int,z int);

step3:load data local inpath 'file1' into table tab2;

step4:selecting all cols
hive>select *from tab2;
1 2 3
4 5 6
7 8 9

hive>selecting x,y from tab2
hive>select x,y from tab2;

In hdfs,

$hadoop fs -ls /user/hive/warehouse/tab2
/user/hive/warehouse/tab2/file1

displaying data from file1
--------------------------
$hadoop fs -cat /user/hive/warehouse/tab2/file1

1 2 3
4 5 6
7 8 9

customized delimiter in a hive table
------------------------------------
create a comma seperated file
1,2,3
4,5,6
7,8,9


step2:create a table with 3 cols
hive>create table tab2(x int,y int,z int);

step3:load data local inpath 'file1' into table tab2;

step4:selecting all cols
hive>select *from tab2;
NULL NULL NULL
NULL NULL NULL
NULL NULL NULL


Note:
In the above we are loading comma seperated file,here the default delimiter of a hive table is ctrl+A/'\001',the file format is not compatible with table format so we get the output as NULL because NULL are stored into the table when we load a comma seperated file.

if we want load a file with delimiters like comma(,),dollar($),hash(#) etc then at the creating time of creating table in a hive itself we should sepcify the customized delimiter


creating a hive table with customized delimiter
-----------------------------------------------
syntax
------
create table tablename(col1 type,......)
row format delimited fields terminated by 'symbol';

ex:
create table tab2(x int,y int,z int)
row format delimited fields terminated by ',';

hive>load data local inpath 'file1' into table tab2;

hive>select *from tab2;
1 2 3
4 5 6
7 8 9