You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@hive.apache.org by Nishant Kelkar <ni...@gmail.com> on 2014/11/03 07:27:25 UTC
Re: Passing NULL Value to Partitioned Field in Table
Hi All,
So I have a table, say *test_table*. It has 3 columns, "a", "b", and "c".
For simplicity, let's say all are of type STRING. Say the table, when
created, was partitioned on fields "a" and "b".
Now, I have the following query:
*INSERT INTO TABLE* *test_table*
*PARTITION* (a="foo", b=NULL)
*SELECT* stack(1,
"this is a test string") *AS* (c)
*FROM* dual;
Here, dual is dummy table with one row and column, with value 1.
The problem is, the above query gives an error:
FAILED: ParseException line 2:22 cannot recognize input near 'NULL' ')'
'SELECT' in constant
Any idea why this is so? I'd like to insert a NULL in place of field "b",
so that my resulting row looks like:
"this is a test string" "foo" NULL
I tried the following query, however, and that seemed to run:
*INSERT INTO TABLE* test_table
*PARTITION* (a="foo", b)
*SELECT* stack(1,
"this is a test string",
*CAST*(NULL *AS* STRING)) *AS* (c, b)
*FROM* dual;
If I understand correctly, it is taking the last value in the resultant row
produced by the stack() UDTF, and assigning it's value to "b" (which is
NULL, which is what I want). But, I'm getting the following row when I do a
"SELECT * .." on test_table:
this is a test string foo *__HIVE_DEFAULT_PARTITION__*
What is this "__HIVE_DEFAULT_PARTITION__"? How can I change it to be NULL?
P.S. I'm new to partitioning in Hive.
Best Regards,
Nishant Kelkar
Re: Passing NULL Value to Partitioned Field in Table
Posted by Nishant Kelkar <ni...@gmail.com>.
Just found a related ticket: https://issues.apache.org/jira/browse/HIVE-1309
Best Regards,
Nishant Kelkar
On Sun, Nov 2, 2014 at 10:32 PM, Nishant Kelkar <ni...@gmail.com>
wrote:
> FYI,
>
> I tried the following query too:
>
> *INSERT INTO TABLE* test_table
> *PARTITION* (a="foo", b=*CAST*(NULL *AS* STRING))
> *SELECT* stack(1,
> "this is a test string") AS (c)
> *FROM* dual;
>
> I get the following error:
>
> FAILED: ParseException line 2:22 cannot recognize input near 'CAST' '('
> 'NULL' in constant
>
> Best Regards,
> Nishant Kelkar
>
> On Sun, Nov 2, 2014 at 10:27 PM, Nishant Kelkar <ni...@gmail.com>
> wrote:
>
>> Hi All,
>>
>> So I have a table, say *test_table*. It has 3 columns, "a", "b", and
>> "c". For simplicity, let's say all are of type STRING. Say the table, when
>> created, was partitioned on fields "a" and "b".
>>
>> Now, I have the following query:
>>
>> *INSERT INTO TABLE* *test_table*
>> *PARTITION* (a="foo", b=NULL)
>> *SELECT* stack(1,
>> "this is a test string") *AS* (c)
>> *FROM* dual;
>>
>> Here, dual is dummy table with one row and column, with value 1.
>>
>> The problem is, the above query gives an error:
>>
>> FAILED: ParseException line 2:22 cannot recognize input near 'NULL' ')'
>> 'SELECT' in constant
>>
>> Any idea why this is so? I'd like to insert a NULL in place of field "b",
>> so that my resulting row looks like:
>>
>> "this is a test string" "foo" NULL
>>
>> I tried the following query, however, and that seemed to run:
>> *INSERT INTO TABLE* test_table
>> *PARTITION* (a="foo", b)
>> *SELECT* stack(1,
>> "this is a test string",
>> *CAST*(NULL *AS* STRING)) *AS* (c, b)
>> *FROM* dual;
>>
>> If I understand correctly, it is taking the last value in the resultant
>> row produced by the stack() UDTF, and assigning it's value to "b" (which is
>> NULL, which is what I want). But, I'm getting the following row when I do a
>> "SELECT * .." on test_table:
>>
>> this is a test string foo *__HIVE_DEFAULT_PARTITION__*
>>
>> What is this "__HIVE_DEFAULT_PARTITION__"? How can I change it to be NULL?
>>
>> P.S. I'm new to partitioning in Hive.
>>
>> Best Regards,
>>
>> Nishant Kelkar
>>
>
>
Re: Passing NULL Value to Partitioned Field in Table
Posted by Nishant Kelkar <ni...@gmail.com>.
FYI,
I tried the following query too:
*INSERT INTO TABLE* test_table
*PARTITION* (a="foo", b=*CAST*(NULL *AS* STRING))
*SELECT* stack(1,
"this is a test string") AS (c)
*FROM* dual;
I get the following error:
FAILED: ParseException line 2:22 cannot recognize input near 'CAST' '('
'NULL' in constant
Best Regards,
Nishant Kelkar
On Sun, Nov 2, 2014 at 10:27 PM, Nishant Kelkar <ni...@gmail.com>
wrote:
> Hi All,
>
> So I have a table, say *test_table*. It has 3 columns, "a", "b", and "c".
> For simplicity, let's say all are of type STRING. Say the table, when
> created, was partitioned on fields "a" and "b".
>
> Now, I have the following query:
>
> *INSERT INTO TABLE* *test_table*
> *PARTITION* (a="foo", b=NULL)
> *SELECT* stack(1,
> "this is a test string") *AS* (c)
> *FROM* dual;
>
> Here, dual is dummy table with one row and column, with value 1.
>
> The problem is, the above query gives an error:
>
> FAILED: ParseException line 2:22 cannot recognize input near 'NULL' ')'
> 'SELECT' in constant
>
> Any idea why this is so? I'd like to insert a NULL in place of field "b",
> so that my resulting row looks like:
>
> "this is a test string" "foo" NULL
>
> I tried the following query, however, and that seemed to run:
> *INSERT INTO TABLE* test_table
> *PARTITION* (a="foo", b)
> *SELECT* stack(1,
> "this is a test string",
> *CAST*(NULL *AS* STRING)) *AS* (c, b)
> *FROM* dual;
>
> If I understand correctly, it is taking the last value in the resultant
> row produced by the stack() UDTF, and assigning it's value to "b" (which is
> NULL, which is what I want). But, I'm getting the following row when I do a
> "SELECT * .." on test_table:
>
> this is a test string foo *__HIVE_DEFAULT_PARTITION__*
>
> What is this "__HIVE_DEFAULT_PARTITION__"? How can I change it to be NULL?
>
> P.S. I'm new to partitioning in Hive.
>
> Best Regards,
>
> Nishant Kelkar
>