sql - 插入到HIVE

Question

我的 hive 脚本有问题。

我想使用两个不同的where子句从同一个表中提取两列到新表中。我正在做下一个:

create database IF NOT EXISTS basedatos_financiera;
use basedatos_financiera;
create table IF NOT EXISTS nasdaq (fecha string,open float,high float,low float,close float,volume float,adjclose float) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';
load data local inpath '/home/Documents/Data/nasdaq.csv' overwrite into table nasdaq;

DROP TABLE IF EXISTS aux_nasdaq;

create table IF NOT EXISTS aux_nasdaq (dif float,predic float);


insert into aux_nasdaq (dif) select open from nasdaq where nasdaq.fecha >= '1971-02-08' and  nasdaq.fecha <='2016-04-01'; 

insert into aux_nasdaq (predic) select close from nasdaq where nasdaq.fecha >= '1971-02-05' and  nasdaq.fecha <='2016-03-31';

这样做，新表aux_nasdaq会像我想要的那样生成两列，但存在空字段的问题，如下所示:

Example of the problem

Answer 1

试试这个，

insert into aux_nasdaq (dif, predic)
SELECT
    CASE
            WHEN nasdaq.fecha >= '1971-02-08' and  nasdaq.fecha <='2016-04-01'
                THEN open
            ELSE NULL
            END AS dif
    CASE
            WHEN nasdaq.fecha >= '1971-02-05' and  nasdaq.fecha <='2016-03-31'
                THEN close
            ELSE NULL
            END AS predic
FROM nasdaq

首先尝试选择查询，是否正确获取数据，然后使用插入。让我知道是否行不通