oracle aide

July 3, 2013

Creating a Dual table in Hive

Filed under: hadoop, hive — Tags: — oracleaide @ 9:57 pm

Supposedly, a database without a DUAL  table is not a database.

Here is how an Oracle patriot could create the DUAL table in Apache Hive.

Let’s say – to make it easier to port Oracle SQL to Hive QL.

my.desktop$ echo 'X' > /tmp/dual.txt

my.desktop$ cat /tmp/dual.txt 
my.desktop$ hive 
hive> create table dual(dummy string);
hive> load data local inpath '/tmp/dual.txt' into table dual;
Copying data from file:/tmp/dual.txt
Copying file: file:/tmp/dual.txt
Loading data to table default.dual
Time taken: 0.24 seconds
hive> select * from dual;
Time taken: 4.036 seconds
select * from default.dual;
Time taken: 0.083 seconds
hive (default)> describe extended dual;
col_name data_type comment
dummy string
Detailed Table Information Table(tableName:dual, dbName:default, owner:me, createTime:1371753438, lastAccessTime:0, retention:0, sd:StorageDescriptor(cols:[FieldSchema(name:dummy, type:string, comment:null)], location:hdfs://localhost:9000/opt/app/hive/warehouse/dual, inputFormat:org.apache.hadoop.mapred.TextInputFormat,, compressed:false, numBuckets:-1, serdeInfo:SerDeInfo(name:null, serializationLib:org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe, parameters:{serialization.format=1}), bucketCols:[], sortCols:[], parameters:{}), partitionKeys:[], parameters:{transient_lastDdlTime=1371753494}, viewOriginalText:null, viewExpandedText:null, tableType:MANAGED_TABLE)
Time taken: 4.343 seconds

hive (default)> describe formatted dual;
col_name data_type comment
# col_name data_type comment
dummy string None
# Detailed Table Information
Database: default
Owner: my
CreateTime: Thu Jun 20 11:37:18 PDT 2013
LastAccessTime: UNKNOWN
Protect Mode: None
Retention: 0
Location: hdfs://localhost:9000/opt/app/hive/warehouse/dual
Table Parameters:
transient_lastDdlTime 1371753494
# Storage Information
SerDe Library: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe
InputFormat: org.apache.hadoop.mapred.TextInputFormat
Compressed: No
Num Buckets: -1
Bucket Columns: []
Sort Columns: []
Storage Desc Params:
serialization.format 1
Time taken: 0.696 seconds
hive (default)>

describe forecasting.dual.dummy;
hive (default)> describe dual.dummy;
col_name data_type comment
dummy string from deserializer
Time taken: 0.055 seconds
hive (default)>

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Please log in using one of these methods to post your comment: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

Blog at

%d bloggers like this: