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 
X
my.desktop$ hive 
hive> create table dual(dummy string);
OK
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
OK
Time taken: 0.24 seconds
hive> select * from dual;
OK
X
Time taken: 4.036 seconds
select * from default.dual;
OK
X
Time taken: 0.083 seconds
hive (default)> describe extended dual;
OK
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, outputFormat:org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat, 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;
OK
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 Type: MANAGED_TABLE
Table Parameters:
transient_lastDdlTime 1371753494
# Storage Information
SerDe Library: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe
InputFormat: org.apache.hadoop.mapred.TextInputFormat
OutputFormat: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat
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;
OK
col_name data_type comment
dummy string from deserializer
Time taken: 0.055 seconds
hive (default)>
Advertisements

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:

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: