You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@nifi.apache.org by Mike Harding <mi...@gmail.com> on 2016/05/09 16:04:22 UTC
SelectHiveQL HiveConnectionPool issues
Hi All,
I'm trying to test out the new SelectHiveQL processor but I'm struggling to
get the HiveConnectionPool configured correctly as I keep getting 'error
getting hive connection'.
I'm setting the database URL to my db 'default' as
*jdbc:mysql://<hostname_of_node_with_hiveserver2>/default*
Nifi is installed on a different node in my cluster so I have set the
hive-site.xml to point to /etc/spark/2.4.0.0-169/0/hive-site.xml
I currently have Hive Authorization = None and HIveServer2 authentication =
none but I still specify a user name used to create the db without a
password.
Would appreciate it if someone could share how they have things configured.
Thanks,
Mike
Re: SelectHiveQL HiveConnectionPool issues
Posted by Mike Harding <mi...@gmail.com>.
Query is "select * from <table_name> limit 1"
Table schema has a map<string, string> column type which is the cause, the
rest are string.
Cheers,
Mike
On Mon, 9 May 2016 at 17:56, Matt Burgess <ma...@gmail.com> wrote:
> Mike,
>
> It shouldn't matter what the underlying format is, as the Hive driver
> should take care of the type coercion. Your error refers to a column that
> is of type JAVA_OBJECT, which in Hive usually happens when you have an
> "interval" type (Added in Hive 1.2.0 [1] but apparently not yet
> documented). Does your select query do things like date arithmetic? If so,
> the SelectHiveQL processor does not currently support interval types, but I
> can take a look. If not, then perhaps one or more of your columns needs
> explicit type coercion in the SELECT query, such that it is recognized as a
> more "conventional" SQL type.
>
> Regards,
> Matt
>
> [1] https://issues.apache.org/jira/browse/HIVE-9792
>
>
> On Mon, May 9, 2016 at 12:34 PM, Mike Harding <mi...@gmail.com>
> wrote:
>
>> aaah of course! Thanks Matt that fixed it.
>> When I run my select query I can now receive the results in CSV but when
>> I select to export it in Avro I get the following exception:
>>
>> [image: Inline images 1]
>>
>> I'm assuming this is happening because the underlying data on HDFS my
>> hive table is reading from is not Avro? its currently standard JSON.
>>
>> Thanks,
>> Mike
>>
>>
>>
>>
>>
>>
>> On 9 May 2016 at 17:09, Matt Burgess <ma...@gmail.com> wrote:
>>
>>> Your URL has a scheme of "mysql", try replacing with "hive2", and also
>>> maybe explicitly setting the port:
>>>
>>> jdbc:hive2://<hostname_of_node_with_hiveserver2>:10000/default
>>>
>>> If that doesn't work, can you see if there is an error/stack trace in
>>> logs/nifi-app.log?
>>>
>>> Regards,
>>> Matt
>>>
>>> On Mon, May 9, 2016 at 12:04 PM, Mike Harding <mi...@gmail.com>
>>> wrote:
>>> > Hi All,
>>> >
>>> > I'm trying to test out the new SelectHiveQL processor but I'm
>>> struggling to
>>> > get the HiveConnectionPool configured correctly as I keep getting
>>> 'error
>>> > getting hive connection'.
>>> >
>>> > I'm setting the database URL to my db 'default' as
>>> > jdbc:mysql://<hostname_of_node_with_hiveserver2>/default
>>> >
>>> > Nifi is installed on a different node in my cluster so I have set the
>>> > hive-site.xml to point to /etc/spark/2.4.0.0-169/0/hive-site.xml
>>> >
>>> > I currently have Hive Authorization = None and HIveServer2
>>> authentication =
>>> > none but I still specify a user name used to create the db without a
>>> > password.
>>> >
>>> > Would appreciate it if someone could share how they have things
>>> configured.
>>> >
>>> > Thanks,
>>> > Mike
>>>
>>
>>
>
Re: SelectHiveQL HiveConnectionPool issues
Posted by Matt Burgess <ma...@gmail.com>.
Mike,
It shouldn't matter what the underlying format is, as the Hive driver
should take care of the type coercion. Your error refers to a column that
is of type JAVA_OBJECT, which in Hive usually happens when you have an
"interval" type (Added in Hive 1.2.0 [1] but apparently not yet
documented). Does your select query do things like date arithmetic? If so,
the SelectHiveQL processor does not currently support interval types, but I
can take a look. If not, then perhaps one or more of your columns needs
explicit type coercion in the SELECT query, such that it is recognized as a
more "conventional" SQL type.
Regards,
Matt
[1] https://issues.apache.org/jira/browse/HIVE-9792
On Mon, May 9, 2016 at 12:34 PM, Mike Harding <mi...@gmail.com>
wrote:
> aaah of course! Thanks Matt that fixed it.
> When I run my select query I can now receive the results in CSV but when I
> select to export it in Avro I get the following exception:
>
> [image: Inline images 1]
>
> I'm assuming this is happening because the underlying data on HDFS my hive
> table is reading from is not Avro? its currently standard JSON.
>
> Thanks,
> Mike
>
>
>
>
>
>
> On 9 May 2016 at 17:09, Matt Burgess <ma...@gmail.com> wrote:
>
>> Your URL has a scheme of "mysql", try replacing with "hive2", and also
>> maybe explicitly setting the port:
>>
>> jdbc:hive2://<hostname_of_node_with_hiveserver2>:10000/default
>>
>> If that doesn't work, can you see if there is an error/stack trace in
>> logs/nifi-app.log?
>>
>> Regards,
>> Matt
>>
>> On Mon, May 9, 2016 at 12:04 PM, Mike Harding <mi...@gmail.com>
>> wrote:
>> > Hi All,
>> >
>> > I'm trying to test out the new SelectHiveQL processor but I'm
>> struggling to
>> > get the HiveConnectionPool configured correctly as I keep getting 'error
>> > getting hive connection'.
>> >
>> > I'm setting the database URL to my db 'default' as
>> > jdbc:mysql://<hostname_of_node_with_hiveserver2>/default
>> >
>> > Nifi is installed on a different node in my cluster so I have set the
>> > hive-site.xml to point to /etc/spark/2.4.0.0-169/0/hive-site.xml
>> >
>> > I currently have Hive Authorization = None and HIveServer2
>> authentication =
>> > none but I still specify a user name used to create the db without a
>> > password.
>> >
>> > Would appreciate it if someone could share how they have things
>> configured.
>> >
>> > Thanks,
>> > Mike
>>
>
>
Re: SelectHiveQL HiveConnectionPool issues
Posted by Mike Harding <mi...@gmail.com>.
aaah of course! Thanks Matt that fixed it.
When I run my select query I can now receive the results in CSV but when I
select to export it in Avro I get the following exception:
[image: Inline images 1]
I'm assuming this is happening because the underlying data on HDFS my hive
table is reading from is not Avro? its currently standard JSON.
Thanks,
Mike
On 9 May 2016 at 17:09, Matt Burgess <ma...@gmail.com> wrote:
> Your URL has a scheme of "mysql", try replacing with "hive2", and also
> maybe explicitly setting the port:
>
> jdbc:hive2://<hostname_of_node_with_hiveserver2>:10000/default
>
> If that doesn't work, can you see if there is an error/stack trace in
> logs/nifi-app.log?
>
> Regards,
> Matt
>
> On Mon, May 9, 2016 at 12:04 PM, Mike Harding <mi...@gmail.com>
> wrote:
> > Hi All,
> >
> > I'm trying to test out the new SelectHiveQL processor but I'm struggling
> to
> > get the HiveConnectionPool configured correctly as I keep getting 'error
> > getting hive connection'.
> >
> > I'm setting the database URL to my db 'default' as
> > jdbc:mysql://<hostname_of_node_with_hiveserver2>/default
> >
> > Nifi is installed on a different node in my cluster so I have set the
> > hive-site.xml to point to /etc/spark/2.4.0.0-169/0/hive-site.xml
> >
> > I currently have Hive Authorization = None and HIveServer2
> authentication =
> > none but I still specify a user name used to create the db without a
> > password.
> >
> > Would appreciate it if someone could share how they have things
> configured.
> >
> > Thanks,
> > Mike
>
Re: SelectHiveQL HiveConnectionPool issues
Posted by Matt Burgess <ma...@gmail.com>.
Your URL has a scheme of "mysql", try replacing with "hive2", and also
maybe explicitly setting the port:
jdbc:hive2://<hostname_of_node_with_hiveserver2>:10000/default
If that doesn't work, can you see if there is an error/stack trace in
logs/nifi-app.log?
Regards,
Matt
On Mon, May 9, 2016 at 12:04 PM, Mike Harding <mi...@gmail.com> wrote:
> Hi All,
>
> I'm trying to test out the new SelectHiveQL processor but I'm struggling to
> get the HiveConnectionPool configured correctly as I keep getting 'error
> getting hive connection'.
>
> I'm setting the database URL to my db 'default' as
> jdbc:mysql://<hostname_of_node_with_hiveserver2>/default
>
> Nifi is installed on a different node in my cluster so I have set the
> hive-site.xml to point to /etc/spark/2.4.0.0-169/0/hive-site.xml
>
> I currently have Hive Authorization = None and HIveServer2 authentication =
> none but I still specify a user name used to create the db without a
> password.
>
> Would appreciate it if someone could share how they have things configured.
>
> Thanks,
> Mike