Hive和Oracle函数对照表

字符函数：
    Oracle                                    Hive
      ||                                      concat
     nvl                                        nvl
    replace                               regexp_replace


Hive:
插入 ''
              > insert overwrite table y2 select id,'' from y1;
Total MapReduce jobs = 3
Launching Job 1 out of 3
Number of reduce tasks is set to 0 since there's no reduce operator
Starting Job = job_1407899713476_1027, Tracking URL = http://132.35.227.211:23188/proxy/application_1407899713476_1027/
Kill Command = /opt/boh-2.0.0/hadoop//bin/hadoop job  -kill job_1407899713476_1027
Hadoop job information for Stage-1: number of mappers: 1; number of reducers: 0
2014-08-22 16:47:26,487 Stage-1 map = 0%,  reduce = 0%
2014-08-22 16:47:32,716 Stage-1 map = 100%,  reduce = 0%, Cumulative CPU 1.59 sec
2014-08-22 16:47:33,754 Stage-1 map = 100%,  reduce = 0%, Cumulative CPU 1.59 sec
MapReduce Total cumulative CPU time: 1 seconds 590 msec
Ended Job = job_1407899713476_1027
Stage-4 is selected by condition resolver.
Stage-3 is filtered out by condition resolver.
Stage-5 is filtered out by condition resolver.
Moving data to: hdfs://boh/hivescratchdir/hive-bonc/hive_2014-08-22_16-47-19_186_741001476537738103-1/-ext-10000
Loading data to table default.y2
rmr: DEPRECATED: Please use 'rm -r' instead.
Deleted /user/hive/warehouse/y2
Table default.y2 stats: [num_partitions: 0, num_files: 1, num_rows: 0, total_size: 51, raw_data_size: 0]
MapReduce Jobs Launched:
Job 0: Map: 1   Cumulative CPU: 1.59 sec   HDFS Read: 259 HDFS Write: 51 SUCCESS
Total MapReduce CPU Time Spent: 1 seconds 590 msec
OK
id      _c1
Time taken: 18.476 seconds
hive (default)>
              >
              >
              > select * from y2;
OK
id      name
1.0
2.0
3.0
4.0
5.0
6.0
7.0
8.0
9.0
10.0
Time taken: 0.098 seconds, Fetched: 10 row(s)
hive (default)>
              >
              >
              > select * from y2 where name is null;
Total MapReduce jobs = 1
Launching Job 1 out of 1
Number of reduce tasks is set to 0 since there's no reduce operator
Starting Job = job_1407899713476_1029, Tracking URL = http://132.35.227.211:23188/proxy/application_1407899713476_1029/
Kill Command = /opt/boh-2.0.0/hadoop//bin/hadoop job  -kill job_1407899713476_1029
Hadoop job information for Stage-1: number of mappers: 1; number of reducers: 0
2014-08-22 16:48:18,223 Stage-1 map = 0%,  reduce = 0%
2014-08-22 16:48:24,547 Stage-1 map = 100%,  reduce = 0%, Cumulative CPU 2.13 sec
2014-08-22 16:48:25,587 Stage-1 map = 100%,  reduce = 0%, Cumulative CPU 2.13 sec
MapReduce Total cumulative CPU time: 2 seconds 130 msec
Ended Job = job_1407899713476_1029
MapReduce Jobs Launched:
Job 0: Map: 1   Cumulative CPU: 2.13 sec   HDFS Read: 247 HDFS Write: 0 SUCCESS
Total MapReduce CPU Time Spent: 2 seconds 130 msec
OK
id      name
Time taken: 16.085 seconds
hive (default)> select * from y2 where name='';    
Total MapReduce jobs = 1
Launching Job 1 out of 1
Number of reduce tasks is set to 0 since there's no reduce operator
Starting Job = job_1407899713476_1030, Tracking URL = http://132.35.227.211:23188/proxy/application_1407899713476_1030/
Kill Command = /opt/boh-2.0.0/hadoop//bin/hadoop job  -kill job_1407899713476_1030
Hadoop job information for Stage-1: number of mappers: 1; number of reducers: 0
2014-08-22 16:48:41,085 Stage-1 map = 0%,  reduce = 0%
2014-08-22 16:48:48,328 Stage-1 map = 100%,  reduce = 0%, Cumulative CPU 2.48 sec
2014-08-22 16:48:49,367 Stage-1 map = 100%,  reduce = 0%, Cumulative CPU 2.48 sec
MapReduce Total cumulative CPU time: 2 seconds 480 msec
Ended Job = job_1407899713476_1030
MapReduce Jobs Launched:
Job 0: Map: 1   Cumulative CPU: 2.48 sec   HDFS Read: 247 HDFS Write: 51 SUCCESS
Total MapReduce CPU Time Spent: 2 seconds 480 msec
OK
id      name
1.0
2.0
3.0
4.0
5.0
6.0
7.0
8.0
9.0
10.0
Time taken: 16.111 seconds, Fetched: 10 row(s)
hive (default)> 


插入 NULL
> insert overwrite table y2 select id,null from y1;
Total MapReduce jobs = 3
Launching Job 1 out of 3
Number of reduce tasks is set to 0 since there's no reduce operator
Starting Job = job_1407899713476_1032, Tracking URL = http://132.35.227.211:23188/proxy/application_1407899713476_1032/
Kill Command = /opt/boh-2.0.0/hadoop//bin/hadoop job  -kill job_1407899713476_1032
Hadoop job information for Stage-1: number of mappers: 1; number of reducers: 0
2014-08-22 16:49:45,965 Stage-1 map = 0%,  reduce = 0%
2014-08-22 16:49:52,154 Stage-1 map = 100%,  reduce = 0%, Cumulative CPU 1.32 sec
2014-08-22 16:49:53,192 Stage-1 map = 100%,  reduce = 0%, Cumulative CPU 1.32 sec
MapReduce Total cumulative CPU time: 1 seconds 320 msec
Ended Job = job_1407899713476_1032
Stage-4 is selected by condition resolver.
Stage-3 is filtered out by condition resolver.
Stage-5 is filtered out by condition resolver.
Moving data to: hdfs://boh/hivescratchdir/hive-bonc/hive_2014-08-22_16-49-39_832_3526498359247127570-1/-ext-10000
Loading data to table default.y2
rmr: DEPRECATED: Please use 'rm -r' instead.
Deleted /user/hive/warehouse/y2
Table default.y2 stats: [num_partitions: 0, num_files: 1, num_rows: 0, total_size: 71, raw_data_size: 0]
MapReduce Jobs Launched:
Job 0: Map: 1   Cumulative CPU: 1.32 sec   HDFS Read: 259 HDFS Write: 71 SUCCESS
Total MapReduce CPU Time Spent: 1 seconds 320 msec
OK
id      _c1
Time taken: 16.474 seconds
hive (default)> select * from y2;
OK
id      name
1.0     NULL
2.0     NULL
3.0     NULL
4.0     NULL
5.0     NULL
6.0     NULL
7.0     NULL
8.0     NULL
9.0     NULL
10.0    NULL
Time taken: 0.097 seconds, Fetched: 10 row(s)
hive (default)>
              >
              > select * from y2 where name = '';
Total MapReduce jobs = 1
Launching Job 1 out of 1
Number of reduce tasks is set to 0 since there's no reduce operator
Starting Job = job_1407899713476_1033, Tracking URL = http://132.35.227.211:23188/proxy/application_1407899713476_1033/
Kill Command = /opt/boh-2.0.0/hadoop//bin/hadoop job  -kill job_1407899713476_1033
Hadoop job information for Stage-1: number of mappers: 1; number of reducers: 0
2014-08-22 16:50:35,422 Stage-1 map = 0%,  reduce = 0%
2014-08-22 16:50:41,657 Stage-1 map = 100%,  reduce = 0%, Cumulative CPU 1.63 sec
2014-08-22 16:50:42,695 Stage-1 map = 100%,  reduce = 0%, Cumulative CPU 1.63 sec
2014-08-22 16:50:43,733 Stage-1 map = 100%,  reduce = 0%, Cumulative CPU 1.63 sec
MapReduce Total cumulative CPU time: 1 seconds 630 msec
Ended Job = job_1407899713476_1033
MapReduce Jobs Launched:
Job 0: Map: 1   Cumulative CPU: 1.63 sec   HDFS Read: 267 HDFS Write: 0 SUCCESS
Total MapReduce CPU Time Spent: 1 seconds 630 msec
OK
id      name
Time taken: 16.099 seconds
hive (default)> select * from y2 where name is null;
Total MapReduce jobs = 1
Launching Job 1 out of 1
Number of reduce tasks is set to 0 since there's no reduce operator
Starting Job = job_1407899713476_1035, Tracking URL = http://132.35.227.211:23188/proxy/application_1407899713476_1035/
Kill Command = /opt/boh-2.0.0/hadoop//bin/hadoop job  -kill job_1407899713476_1035
Hadoop job information for Stage-1: number of mappers: 1; number of reducers: 0
2014-08-22 16:51:00,080 Stage-1 map = 0%,  reduce = 0%
2014-08-22 16:51:07,330 Stage-1 map = 100%,  reduce = 0%, Cumulative CPU 2.33 sec
2014-08-22 16:51:08,359 Stage-1 map = 100%,  reduce = 0%, Cumulative CPU 2.33 sec
MapReduce Total cumulative CPU time: 2 seconds 330 msec
Ended Job = job_1407899713476_1035
MapReduce Jobs Launched:
Job 0: Map: 1   Cumulative CPU: 2.33 sec   HDFS Read: 267 HDFS Write: 71 SUCCESS
Total MapReduce CPU Time Spent: 2 seconds 330 msec
OK
id      name
1.0     NULL
2.0     NULL
3.0     NULL
4.0     NULL
5.0     NULL
6.0     NULL
7.0     NULL
8.0     NULL
9.0     NULL
10.0    NULL
Time taken: 16.11 seconds, Fetched: 10 row(s)
hive (default)>

Oracle:

5,6行插入的是 null
9,10行插入的是 ''
SELECT * FROM Y1;

SELECT * FROM Y1 WHERE NAME ='';

SELECT * FROM Y1 WHERE NAME IS NULL;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/28520410/viewspace-1254210/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/28520410/viewspace-1254210/