Re: Creating View Table Using the Date & Time

[Vaghawan Ojha]

Ok, Thank you very much for the help.

On Wed, Feb 14, 2018 at 12:43 PM, James Taylor <jamestay...@apache.org>
wrote:

> No, you’ll need to create a Phoenix table and use Phoenix APIs to write
> your data.
>
> On Tue, Feb 13, 2018 at 9:52 PM Vaghawan Ojha <vaghawan...@gmail.com>
> wrote:
>
>> Thank you James, my keys are something like this: 2018-02-01-BM50558-
>> 1517454912.0-5-1517548497.261604 . the first few chars are the date. and
>> these dates are stored in a seperate columns as BDATE as well. Do you think
>> I could implement the rowtimestamp in the BDATE column?
>>
>> Thanks
>> Vaghawan
>>
>> On Wed, Feb 14, 2018 at 7:47 AM, James Taylor <jamestay...@apache.org>
>> wrote:
>>
>>> If the date time column is part of your pk, then you’d be able to use
>>> the ROW_TIMESTAMP feature.
>>>
>>> On Tue, Feb 13, 2018 at 5:04 PM Vaghawan Ojha <vaghawan...@gmail.com>
>>> wrote:
>>>
>>>> Yes, the datetime column is part of my primary key, but primary key
>>>> also consists other strings.
>>>>
>>>> Thanks
>>>> Vaghawan
>>>>
>>>> On Tue, Feb 13, 2018 at 11:05 PM, James Taylor <jamestay...@apache.org>
>>>> wrote:
>>>>
>>>>> The standard way of doing this is to add a TTL for your table [1]. You
>>>>> can do this through the ALTER TABLE call [2]. Is the date/time column part
>>>>> of your primary key? If so, you can improve performance by declaring this
>>>>> column as a ROW_TIMESTAMP [3].
>>>>>
>>>>> A view is not going to help you - it's not materialized.
>>>>>
>>>>> Thanks,
>>>>> James
>>>>>
>>>>> [1] http://hbase.apache.org/0.94/book/ttl.html
>>>>> [2] https://phoenix.apache.org/language/index.html#alter
>>>>> [3] https://phoenix.apache.org/rowtimestamp.html
>>>>>
>>>>> On Tue, Feb 13, 2018 at 2:42 AM, Vaghawan Ojha <vaghawan...@gmail.com>
>>>>> wrote:
>>>>>
>>>>>> Hi,
>>>>>>
>>>>>> I'm using phoenix 4.12 with hbase 1.2.0, I've a table with few
>>>>>> millions of rows, but I don't need much of the old data, Let's say the
>>>>>> frequent data I need is data from 2 month back.
>>>>>>
>>>>>> the query become slow when I read the table using timestamp. So query
>>>>>> would be like where date>some date and >>>>>
>>>>>> I was thinking of creating a veiw table where I could put the latest
>>>>>> two months data, but the data there should consist only the latest two
>>>>>> months. The parent table is updated daily with the new data, so in my 
>>>>>> case
>>>>>> whenever new day's data comes in, the last one day's data should be 
>>>>>> removed
>>>>>> from the view, making sure that the view consists two month's data. (e.g 
>>>>>> it
>>>>>> would always hold last 60 days data)
>>>>>> I don't know if that is possible using create view. If it is how can
>>>>>> I do it?
>>>>>>
>>>>>> Any suggestion would be appreciated.
>>>>>>
>>>>>> Thanks
>>>>>> Vaghawan
>>>>>>
>>>>>
>>>>>
>>>>
>>

Re: Creating View Table Using the Date & Time

[Vaghawan Ojha]

Thank you James, my keys are something like
this: 2018-02-01-BM50558-1517454912.0-5-1517548497.261604 . the first few
chars are the date. and these dates are stored in a seperate columns as
BDATE as well. Do you think I could implement the rowtimestamp in the BDATE
column?

Thanks
Vaghawan

On Wed, Feb 14, 2018 at 7:47 AM, James Taylor <jamestay...@apache.org>
wrote:

> If the date time column is part of your pk, then you’d be able to use the
> ROW_TIMESTAMP feature.
>
> On Tue, Feb 13, 2018 at 5:04 PM Vaghawan Ojha <vaghawan...@gmail.com>
> wrote:
>
>> Yes, the datetime column is part of my primary key, but primary key also
>> consists other strings.
>>
>> Thanks
>> Vaghawan
>>
>> On Tue, Feb 13, 2018 at 11:05 PM, James Taylor <jamestay...@apache.org>
>> wrote:
>>
>>> The standard way of doing this is to add a TTL for your table [1]. You
>>> can do this through the ALTER TABLE call [2]. Is the date/time column part
>>> of your primary key? If so, you can improve performance by declaring this
>>> column as a ROW_TIMESTAMP [3].
>>>
>>> A view is not going to help you - it's not materialized.
>>>
>>> Thanks,
>>> James
>>>
>>> [1] http://hbase.apache.org/0.94/book/ttl.html
>>> [2] https://phoenix.apache.org/language/index.html#alter
>>> [3] https://phoenix.apache.org/rowtimestamp.html
>>>
>>> On Tue, Feb 13, 2018 at 2:42 AM, Vaghawan Ojha <vaghawan...@gmail.com>
>>> wrote:
>>>
>>>> Hi,
>>>>
>>>> I'm using phoenix 4.12 with hbase 1.2.0, I've a table with few millions
>>>> of rows, but I don't need much of the old data, Let's say the frequent data
>>>> I need is data from 2 month back.
>>>>
>>>> the query become slow when I read the table using timestamp. So query
>>>> would be like where date>some date and >>>
>>>> I was thinking of creating a veiw table where I could put the latest
>>>> two months data, but the data there should consist only the latest two
>>>> months. The parent table is updated daily with the new data, so in my case
>>>> whenever new day's data comes in, the last one day's data should be removed
>>>> from the view, making sure that the view consists two month's data. (e.g it
>>>> would always hold last 60 days data)
>>>> I don't know if that is possible using create view. If it is how can I
>>>> do it?
>>>>
>>>> Any suggestion would be appreciated.
>>>>
>>>> Thanks
>>>> Vaghawan
>>>>
>>>
>>>
>>

Re: Creating View Table Using the Date & Time

[Vaghawan Ojha]

Yes, the datetime column is part of my primary key, but primary key also
consists other strings.

Thanks
Vaghawan

On Tue, Feb 13, 2018 at 11:05 PM, James Taylor <jamestay...@apache.org>
wrote:

> The standard way of doing this is to add a TTL for your table [1]. You can
> do this through the ALTER TABLE call [2]. Is the date/time column part of
> your primary key? If so, you can improve performance by declaring this
> column as a ROW_TIMESTAMP [3].
>
> A view is not going to help you - it's not materialized.
>
> Thanks,
> James
>
> [1] http://hbase.apache.org/0.94/book/ttl.html
> [2] https://phoenix.apache.org/language/index.html#alter
> [3] https://phoenix.apache.org/rowtimestamp.html
>
> On Tue, Feb 13, 2018 at 2:42 AM, Vaghawan Ojha <vaghawan...@gmail.com>
> wrote:
>
>> Hi,
>>
>> I'm using phoenix 4.12 with hbase 1.2.0, I've a table with few millions
>> of rows, but I don't need much of the old data, Let's say the frequent data
>> I need is data from 2 month back.
>>
>> the query become slow when I read the table using timestamp. So query
>> would be like where date>some date and >
>> I was thinking of creating a veiw table where I could put the latest two
>> months data, but the data there should consist only the latest two months.
>> The parent table is updated daily with the new data, so in my case whenever
>> new day's data comes in, the last one day's data should be removed from the
>> view, making sure that the view consists two month's data. (e.g it would
>> always hold last 60 days data)
>> I don't know if that is possible using create view. If it is how can I do
>> it?
>>
>> Any suggestion would be appreciated.
>>
>> Thanks
>> Vaghawan
>>
>
>

Creating View Table Using the Date & Time

[Vaghawan Ojha]

Hi,

I'm using phoenix 4.12 with hbase 1.2.0, I've a table with few millions of
rows, but I don't need much of the old data, Let's say the frequent data I
need is data from 2 month back.

the query become slow when I read the table using timestamp. So query would
be like where date>some date and 

Re: HBase Timeout on queries

[Vaghawan Ojha]

I've the same problem, even after I increased the hbase.rpc.timeout the
result is same. The difference is that I use 4.12.


On Thu, Feb 1, 2018 at 8:23 PM, Flavio Pompermaier 
wrote:

> Hi to all,
> I'm trying to use the brand-new Phoenix 4.13.2-cdh5.11.2 over HBase and
> everything was fine until the data was quite small (about few millions). As
> I inserted 170 M of rows in my table I cannot get the row count anymore
> (using ELECT COUNT) because of org.apache.hbase.ipc.CallTimeoutException
> (operationTimeout 6 expired).
> How can I fix this problem? I could increase the hbase.rpc.timeout
> parameter but I suspect I could improve a little bit the HBase performance
> first..the problem is that I don't know how.
>
> Thanks in advance,
> Flavio
>

Re: Problem With QueryServer In SQL Line Thin Client.

[Vaghawan Ojha]

Hi Josh,

I fixed it by specifying the host where the query server was running while
doing python queryserver.py stop.

Something like this: python queryserver.py stop localhost:8765

And again started normally, it started working.

Thanks a lot.
Vaghawan

On Fri, Dec 8, 2017 at 7:38 AM, Vaghawan Ojha <vaghawan...@gmail.com> wrote:

> Hi Josh,
>
> Here's the full stacktrace from PQS, I think its saying the PQS at
> localhost has an error.
>
> python sqlline-thin.py bigdata-namenode:8765
> Failed to extract serialization from hbase-site.xml, defaulting to
> PROTOBUF.
> Setting property: [incremental, false]
> Setting property: [isolation, TRANSACTION_READ_COMMITTED]
> issuing: !connect 
> jdbc:phoenix:thin:url=http://bigdata-namenode:8765;serialization=PROTOBUF
> none none org.apache.phoenix.queryserver.client.Driver
> Connecting to jdbc:phoenix:thin:url=http://bigdata-namenode:8765;
> serialization=PROTOBUF
> SLF4J: Class path contains multiple SLF4J bindings.
> SLF4J: Found binding in [jar:file:/home/saque/hadoopec/phoenix/apache-
> phoenix-4.12.0-HBase-1.2-bin/phoenix-4.12.0-HBase-1.2-thin-
> client.jar!/org/slf4j/impl/StaticLoggerBinder.class]
> SLF4J: Found binding in [jar:file:/home/saque/
> hadoopec/hadoop/share/hadoop/common/lib/slf4j-log4j12-1.7.
> 10.jar!/org/slf4j/impl/StaticLoggerBinder.class]
> SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an
> explanation.
> AvaticaClientRuntimeException: Remote driver error: RuntimeException:
> java.sql.SQLException: No suitable driver found for localhost ->
> SQLException: No suitable driver found for localhost. Error -1 (0) null
>
> java.lang.RuntimeException: java.sql.SQLException: No suitable driver
> found for localhost
> at org.apache.calcite.avatica.jdbc.JdbcMeta.openConnection(
> JdbcMeta.java:621)
> at org.apache.calcite.avatica.remote.LocalService.apply(
> LocalService.java:285)
> at org.apache.calcite.avatica.remote.Service$OpenConnectionRequest.accept(
> Service.java:1771)
> at org.apache.calcite.avatica.remote.Service$OpenConnectionRequest.accept(
> Service.java:1751)
> at org.apache.calcite.avatica.remote.AbstractHandler.apply(
> AbstractHandler.java:94)
> at org.apache.calcite.avatica.remote.ProtobufHandler.apply(
> ProtobufHandler.java:46)
> at org.apache.calcite.avatica.server.AvaticaProtobufHandler.
> handle(AvaticaProtobufHandler.java:127)
> at org.apache.phoenix.shaded.org.eclipse.jetty.server.handler.
> HandlerList.handle(HandlerList.java:52)
> at org.apache.phoenix.shaded.org.eclipse.jetty.server.handler.
> HandlerWrapper.handle(HandlerWrapper.java:97)
> at org.apache.phoenix.shaded.org.eclipse.jetty.server.Server.
> handle(Server.java:499)
> at org.apache.phoenix.shaded.org.eclipse.jetty.server.HttpChannel.handle(
> HttpChannel.java:311)
> at org.apache.phoenix.shaded.org.eclipse.jetty.server.
> HttpConnection.onFillable(HttpConnection.java:257)
> at org.apache.phoenix.shaded.org.eclipse.jetty.io.
> AbstractConnection$2.run(AbstractConnection.java:544)
> at org.apache.phoenix.shaded.org.eclipse.jetty.util.thread.
> QueuedThreadPool.runJob(QueuedThreadPool.java:635)
> at org.apache.phoenix.shaded.org.eclipse.jetty.util.thread.
> QueuedThreadPool$3.run(QueuedThreadPool.java:555)
> at java.lang.Thread.run(Thread.java:748)
> Caused by: java.sql.SQLException: No suitable driver found for localhost
> at java.sql.DriverManager.getConnection(DriverManager.java:689)
> at java.sql.DriverManager.getConnection(DriverManager.java:208)
> at org.apache.calcite.avatica.jdbc.JdbcMeta.openConnection(
> JdbcMeta.java:618)
> ... 15 more
>
>
> at org.apache.calcite.avatica.remote.Service$ErrorResponse.
> toException(Service.java:2476)
> at org.apache.calcite.avatica.remote.RemoteProtobufService._
> apply(RemoteProtobufService.java:63)
> at org.apache.calcite.avatica.remote.ProtobufService.apply(
> ProtobufService.java:81)
> at org.apache.calcite.avatica.remote.Driver.connect(Driver.java:176)
> at sqlline.DatabaseConnection.connect(DatabaseConnection.java:157)
> at sqlline.DatabaseConnection.getConnection(DatabaseConnection.java:203)
> at sqlline.Commands.connect(Commands.java:1064)
> at sqlline.Commands.connect(Commands.java:996)
> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
> at sun.reflect.NativeMethodAccessorImpl.invoke(
> NativeMethodAccessorImpl.java:62)
> at sun.reflect.DelegatingMethodAccessorImpl.invoke(
> DelegatingMethodAccessorImpl.java:43)
> at java.lang.reflect.Method.invoke(Method.java:498)
> at sqlline.ReflectiveCommandHandler.execute(ReflectiveCommandHandler.java:
> 38)
> at sqlline.SqlLine.dispatch(SqlLine.java:809)
> at sqlline.SqlLine.initArgs(SqlLine.java:588)
> at sqlline.SqlLine.begin(SqlLine.java:661)
> at

Re: Problem With QueryServer In SQL Line Thin Client.

[Vaghawan Ojha]

vice.java:81)
at org.apache.calcite.avatica.remote.Driver.connect(Driver.java:176)
at sqlline.DatabaseConnection.connect(DatabaseConnection.java:157)
at sqlline.DatabaseConnection.getConnection(DatabaseConnection.java:203)
at sqlline.Commands.close(Commands.java:906)
at sqlline.Commands.closeall(Commands.java:880)
at sqlline.SqlLine.begin(SqlLine.java:714)
at sqlline.SqlLine.start(SqlLine.java:398)
at sqlline.SqlLine.main(SqlLine.java:291)
at
org.apache.phoenix.queryserver.client.SqllineWrapper.main(SqllineWrapper.java:93)


Here the bigdata-namenode is like localhost, it's a host name with a local
ip. It neither works with localhost nor with the local ip.

Thank you


On Thu, Dec 7, 2017 at 10:26 PM, Josh Elser <els...@apache.org> wrote:

> I can't seem to track down that error message to any specific line of code.
>
> Do you have a stacktrace in the PQS log? I'm not sure the the message is
> implying that "localhost" is being interpreted as a class name or if it's
> saying the PQS at localhost had an error. The more details you can provide,
> the better.
>
>
> On 12/7/17 12:55 AM, Vaghawan Ojha wrote:
>
>> Hi,
>>
>> I'm facing a weird problem with queryserver. When I start the
>> queryserver, the log says that the service is listening in the port 8765,
>> everything looks fine.
>>
>> But when I try to use the queryserver, the error like this prompts up.
>>
>> n$ python sqlline-thin.py localhost:8765
>> Failed to extract serialization from hbase-site.xml, defaulting to
>> PROTOBUF.
>> Setting property: [incremental, false]
>> Setting property: [isolation, TRANSACTION_READ_COMMITTED]
>> issuing: !connect jdbc:phoenix:thin:url=http://l
>> ocalhost:8765;serialization=PROTOBUF none none
>> org.apache.phoenix.queryserver.client.Driver
>> Connecting to jdbc:phoenix:thin:url=http://l
>> ocalhost:8765;serialization=PROTOBUF
>> SLF4J: Class path contains multiple SLF4J bindings.
>> SLF4J: Found binding in [jar:file:/home/saque/hadoopec
>> /phoenix/apache-phoenix-4.12.0-HBase-1.2-bin/phoenix-4.12.
>> 0-HBase-1.2-thin-client.jar!/org/slf4j/impl/StaticLoggerBinder.class]
>> SLF4J: Found binding in [jar:file:/home/saque/hadoopec
>> /hadoop/share/hadoop/common/lib/slf4j-log4j12-1.7.10.jar!/
>> org/slf4j/impl/StaticLoggerBinder.class]
>> SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an
>> explanation.
>> AvaticaClientRuntimeException: Remote driver error: RuntimeException:
>> java.sql.SQLException: No suitable driver found for localhost ->
>> SQLException: No suitable driver found for localhost. Error -1 (0) null
>>
>> java.lang.RuntimeException: java.sql.SQLException: No suitable driver
>> found for localhost
>>
>> The same queryserver was running before the server was restarted. This
>> happens all the time, when I restart the server. The hadoop classpath and
>> hbase classpath are there. The sqlline with zookeper port works fine.
>>
>> When I do: python sqlline.py localhost:2181. It works. However when I do
>> python sqlline-thin.py localhost:8765, it throws such errors.
>>
>> Any help would be great, I'm using apache-phoenix-4.12.0-HBase-1.2 .
>>
>> Thanks in advance
>> Vaghawan
>>
>

Problem With QueryServer In SQL Line Thin Client.

[Vaghawan Ojha]

Hi,

I'm facing a weird problem with queryserver. When I start the queryserver,
the log says that the service is listening in the port 8765, everything
looks fine.

But when I try to use the queryserver, the error like this prompts up.

n$ python sqlline-thin.py localhost:8765
Failed to extract serialization from hbase-site.xml, defaulting to PROTOBUF.
Setting property: [incremental, false]
Setting property: [isolation, TRANSACTION_READ_COMMITTED]
issuing: !connect
jdbc:phoenix:thin:url=http://localhost:8765;serialization=PROTOBUF
none none org.apache.phoenix.queryserver.client.Driver
Connecting to jdbc:phoenix:thin:url=http://localhost:8765;serialization
=PROTOBUF
SLF4J: Class path contains multiple SLF4J bindings.
SLF4J: Found binding in
[jar:file:/home/saque/hadoopec/phoenix/apache-phoenix-4.12.0-HBase-1.2-bin/phoenix-4.12.0-HBase-1.2-thin-client.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: Found binding in
[jar:file:/home/saque/hadoopec/hadoop/share/hadoop/common/lib/slf4j-log4j12-1.7.10.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an
explanation.
AvaticaClientRuntimeException: Remote driver error: RuntimeException:
java.sql.SQLException: No suitable driver found for localhost ->
SQLException: No suitable driver found for localhost. Error -1 (0) null

java.lang.RuntimeException: java.sql.SQLException: No suitable driver found
for localhost

The same queryserver was running before the server was restarted. This
happens all the time, when I restart the server. The hadoop classpath and
hbase classpath are there. The sqlline with zookeper port works fine.

When I do: python sqlline.py localhost:2181. It works. However when I do
python sqlline-thin.py localhost:8765, it throws such errors.

Any help would be great, I'm using apache-phoenix-4.12.0-HBase-1.2 .

Thanks in advance
Vaghawan

Re: Reading Phoenix Upserted data directly from Hbase

[Vaghawan Ojha]

Hi Jacobo,

Thanks a lot. This does helps.



On Mon, Dec 4, 2017 at 7:28 PM, Jacobo Coll <jacobo...@gmail.com> wrote:

> You could read the values using the different PDataType in java. You only
> need to know the type of data stored in every column.
>
> ```
> byte[] value;
>
> Integer anInt = (Integer) PInteger.INSTANCE.toObject(value);
> Array array = (Array) PVarcharArray.INSTANCE.toObject(value);
> ```
>
> Reading multiple values from the RowKey is more complicated, as they are
> concatenated, and you have to deal with nullable values and separators..
> but if it only contains one value, is the same procedure.
>
> I hope this helps,
> Jacobo Coll
>
>
> 2017-12-04 11:25 GMT+00:00 Vaghawan Ojha <vaghawan...@gmail.com>:
>
>> Thank you very much for the response, it helps. So I'm guessing there is
>> no way to read the data from hbase directly which are inserted through
>> phoenix.
>>
>> Thanks
>> Vaghawan
>>
>> On Sat, Dec 2, 2017 at 1:20 AM, Sergey Soldatov <sergeysolda...@gmail.com
>> > wrote:
>>
>>> HBase doesn't know about data types that you are using in Phoenix. So it
>>> operates with binary arrays. HBase shell shows printable ASCII characters
>>> as is and hex values for the rest. You may use spark-phoenix module to work
>>> with Phoenix from Spark.
>>>
>>> Thanks,
>>> Sergey
>>>
>>> On Thu, Nov 30, 2017 at 11:22 PM, Vaghawan Ojha <vaghawan...@gmail.com>
>>> wrote:
>>>
>>>> Hi,
>>>>
>>>> I've few phoenix tables created from Phoenix itself, they do fine with
>>>> the Phoenix, however, when I try to scan the data from hbase shell, the
>>>> binary strings get printed instead of the real values, like the one I can
>>>> see in the phoenix.
>>>>
>>>> Also, there are cases when I want to fetch them directly from hbase and
>>>> work with spark. I guess I'm doing something wrong with the configuration
>>>> of phoenix, or is this the expected result?
>>>>
>>>> I'm using phoenix-4.12.0-HBase-1.2 .
>>>>
>>>> Any reply would be appreciated.
>>>>
>>>> Thanks
>>>>
>>>
>>>
>>
>

Re: Reading Phoenix Upserted data directly from Hbase

[Vaghawan Ojha]

Thank you very much for the response, it helps. So I'm guessing there is no
way to read the data from hbase directly which are inserted through
phoenix.

Thanks
Vaghawan

On Sat, Dec 2, 2017 at 1:20 AM, Sergey Soldatov <sergeysolda...@gmail.com>
wrote:

> HBase doesn't know about data types that you are using in Phoenix. So it
> operates with binary arrays. HBase shell shows printable ASCII characters
> as is and hex values for the rest. You may use spark-phoenix module to work
> with Phoenix from Spark.
>
> Thanks,
> Sergey
>
> On Thu, Nov 30, 2017 at 11:22 PM, Vaghawan Ojha <vaghawan...@gmail.com>
> wrote:
>
>> Hi,
>>
>> I've few phoenix tables created from Phoenix itself, they do fine with
>> the Phoenix, however, when I try to scan the data from hbase shell, the
>> binary strings get printed instead of the real values, like the one I can
>> see in the phoenix.
>>
>> Also, there are cases when I want to fetch them directly from hbase and
>> work with spark. I guess I'm doing something wrong with the configuration
>> of phoenix, or is this the expected result?
>>
>> I'm using phoenix-4.12.0-HBase-1.2 .
>>
>> Any reply would be appreciated.
>>
>> Thanks
>>
>
>

Reading Phoenix Upserted data directly from Hbase

[Vaghawan Ojha]

Hi,

I've few phoenix tables created from Phoenix itself, they do fine with the
Phoenix, however, when I try to scan the data from hbase shell, the binary
strings get printed instead of the real values, like the one I can see in
the phoenix.

Also, there are cases when I want to fetch them directly from hbase and
work with spark. I guess I'm doing something wrong with the configuration
of phoenix, or is this the expected result?

I'm using phoenix-4.12.0-HBase-1.2 .

Any reply would be appreciated.

Thanks

Re: Phoenix CSV bulk loader errors

[Vaghawan Ojha]

Hi, Are you sure you are pointing to the right path and file? Because the
error says
Caused by: java.io.FileNotFoundException: File does not exist:
hdfs://*
Please make sure the csv file is there.

On Sunday, November 26, 2017, idosenesh  wrote:

> Im trying to bulk load into Phoenix using the CsvBulkLoadTool.
> Im running on amazon EMR cluster with 3 i3x2large core nodes, and default
> phoenix/hbase/emr configurations.
>
> I've successfully ran the job 3 times (i.e. succesfully inserted about 250G
> * 3 sized csv files) but the 4th run yields the following error:
> 2017-11-23 21:53:07,962 FATAL [IPC Server handler 7 on 39803]
> org.apache.hadoop.mapred.TaskAttemptListenerImpl: Task:
> attempt_1511332372804_0016_m_002760_1 - exited :
> java.lang.IllegalArgumentException: Can't read partitions file
> at
> org.apache.hadoop.mapreduce.lib.partition.TotalOrderPartitioner.setConf(
> TotalOrderPartitioner.java:116)
> at org.apache.hadoop.util.ReflectionUtils.setConf(
> ReflectionUtils.java:76)
> at
> org.apache.hadoop.util.ReflectionUtils.newInstance(
> ReflectionUtils.java:136)
> at
> org.apache.hadoop.mapred.MapTask$NewOutputCollector.<
> init>(MapTask.java:711)
> at org.apache.hadoop.mapred.MapTask.runNewMapper(MapTask.java:779)
> at org.apache.hadoop.mapred.MapTask.run(MapTask.java:342)
> at org.apache.hadoop.mapred.YarnChild$2.run(YarnChild.java:164)
> at java.security.AccessController.doPrivileged(Native Method)
> at javax.security.auth.Subject.doAs(Subject.java:422)
> at
> org.apache.hadoop.security.UserGroupInformation.doAs(
> UserGroupInformation.java:1698)
> at org.apache.hadoop.mapred.YarnChild.main(YarnChild.java:158)
> Caused by: java.io.FileNotFoundException: File does not exist:
> hdfs://***:8020/mnt/var/lib/hadoop/tmp/
> partitions_66f309d7-fe46-440a-99bb-fd8f3b40099e
> at
> org.apache.hadoop.hdfs.DistributedFileSystem$22.
> doCall(DistributedFileSystem.java:1309)
> at
> org.apache.hadoop.hdfs.DistributedFileSystem$22.
> doCall(DistributedFileSystem.java:1301)
> at
> org.apache.hadoop.fs.FileSystemLinkResolver.resolve(
> FileSystemLinkResolver.java:81)
> at
> org.apache.hadoop.hdfs.DistributedFileSystem.getFileStatus(
> DistributedFileSystem.java:1317)
> at org.apache.hadoop.io.SequenceFile$Reader.(
> SequenceFile.java:1830)
> at org.apache.hadoop.io.SequenceFile$Reader.(
> SequenceFile.java:1853)
> at
> org.apache.hadoop.mapreduce.lib.partition.TotalOrderPartitioner.
> readPartitions(TotalOrderPartitioner.java:301)
> at
> org.apache.hadoop.mapreduce.lib.partition.TotalOrderPartitioner.setConf(
> TotalOrderPartitioner.java:88)
>
>
> My hdfs utilization is not high:
> [hadoop@ /]$ hdfs dfsadmin -report
> Configured Capacity: 5679504728064 (5.17 TB)
> Present Capacity: 5673831846248 (5.16 TB)
> DFS Remaining: 536719720 (4.85 TB)
> DFS Used: 340495126528 (317.11 GB)
> DFS Used%: 6.00%
> Under replicated blocks: 0
> Blocks with corrupt replicas: 0
> Missing blocks: 0
> Missing blocks (with replication factor 1): 0
>
>
>
> Im running the following command:
> HADOOP_CLASSPATH=/usr/lib/hbase/hbase-protocol.jar:/usr/lib/hbase/conf
> hadoop jar /usr/lib/phoenix/phoenix-4.11.0-HBase-1.3-client.jar
> org.apache.phoenix.mapreduce.CsvBulkLoadTool -Dfs.permissions.umask-mode=
> 000
> --table KEYWORDS_COMBINED_SALTED -d '|' --ignore-errors --input
> s3://path/to/my/bucket/file.csv
>
>
> The data on the last table is structurally the same as inserted before.
>
> Any ideas?
>
>
>
> --
> Sent from: http://apache-phoenix-user-list.1124778.n5.nabble.com/
>

Re: Queryserver timeouts for the multiple simultaneous Requests

[Vaghawan Ojha]

Actually it seems, when I have two requests, both jobs seems to crash
throwing the following errors:

phoenixdb.errors.InternalError: ("RuntimeException:
org.apache.phoenix.exception.PhoenixIOException:
org.apache.phoenix.exception.PhoenixIOException: Failed after attempts=36,
exceptions:\nThu Nov 23 12:02:31 UTC 2017, null,
java.net.SocketTimeoutException: callTimeout=6, callDuration=60108: row
'2017-07-01-BM8955-1498884512.0-386-1509295361.5' on table 'BGALLSALES' at
region=BGALLSALES,2017-06-24-BM8912-1498306109.0-58-1509294220.81,1510055260213.c2fbd3a07c560958f5944cda3c29c04f.,
hostname=bigdata-datanode,16020,1511427352525, seqNum=330973\n ->
PhoenixIOException: org.apache.phoenix.exception.PhoenixIOException: Failed
after attempts=36, exceptions:\nThu Nov 23 12:02:31 UTC 2017, null,
java.net.SocketTimeoutException: callTimeout=6, callDuration=60108: row
'2017-07-01-BM8955-1498884512.0-386-1509295361.5' on table 'BGALLSALES' at
region=BGALLSALES,2017-06-24-BM8912-1498306109.0-58-1509294220.81,1510055260213.c2fbd3a07c560958f5944cda3c29c04f.,
hostname=bigdata-datanode,16020,1511427352525, seqNum=330973\n ->
ExecutionException: org.apache.phoenix.exception.PhoenixIOException: Failed
after attempts=36, exceptions:\nThu Nov 23 12:02:31 UTC 2017, null,
java.net.SocketTimeoutException: callTimeout=6, callDuration=60108: row
'2017-07-01-BM8955-1498884512.0-386-1509295361.5' on table 'BGALLSALES' at
region=BGALLSALES,2017-06-24-BM8912-1498306109.0-58-1509294220.81,1510055260213.c2fbd3a07c560958f5944cda3c29c04f.,
hostname=bigdata-datanode,16020,1511427352525, seqNum=330973\n ->
PhoenixIOException: Failed after attempts=36, exceptions:\nThu Nov 23
12:02:31 UTC 2017, null, java.net.SocketTimeoutException:
callTimeout=6, callDuration=60108: row
'2017-07-01-BM8955-1498884512.0-386-1509295361.5' on table 'BGALLSALES' at
region=BGALLSALES,2017-06-24-BM8912-1498306109.0-58-1509294220.81,1510055260213.c2fbd3a07c560958f5944cda3c29c04f.,
hostname=bigdata-datanode,16020,1511427352525, seqNum=330973\n ->
PhoenixIOException: Failed after attempts=36, exceptions:\nThu Nov 23
12:02:31 UTC 2017, null, java.net.SocketTimeoutException:
callTimeout=6, callDuration=60108: row
'2017-07-01-BM8955-1498884512.0-386-1509295361.5' on table 'BGALLSALES' at
region=BGALLSALES,2017-06-24-BM8912-1498306109.0-58-1509294220.81,1510055260213.c2fbd3a07c560958f5944cda3c29c04f.,
hostname=bigdata-datanode,16020,1511427352525, seqNum=330973\n ->
RetriesExhaustedException: Failed after attempts=36, exceptions:\nThu Nov
23 12:02:31 UTC 2017, null, java.net.SocketTimeoutException:
callTimeout=6, callDuration=60108: row
'2017-07-01-BM8955-1498884512.0-386-1509295361.5' on table 'BGALLSALES' at
region=BGALLSALES,2017-06-24-BM8912-1498306109.0-58-1509294220.81,1510055260213.c2fbd3a07c560958f5944cda3c29c04f.,
hostname=bigdata-datanode,16020,1511427352525, seqNum=330973\n ->
SocketTimeoutException: callTimeout=6, callDuration=60108: row
'2017-07-01-BM8955-1498884512.0-386-1509295361.5' on table 'BGALLSALES' at
region=BGALLSALES,2017-06-24-BM8912-1498306109.0-58-1509294220.81,1510055260213.c2fbd3a07c560958f5944cda3c29c04f.,
hostname=bigdata-datanode,16020,1511427352525, seqNum=330973 ->
IOException: Call to bigdata-datanode/10.10.10.166:16020 failed on local
exception: org.apache.hadoop.hbase.ipc.CallTimeoutException: Call id=48582,
waitTime=60004, operationTimeout=6 expired. -> CallTimeoutException:
Call id=48582, waitTime=60004, operationTimeout=6 expired.", None,
None, None)


I've changed the hbase configuration like this:

  
  phoenix.query.timeoutMs
  180
  
  
  hbase.regionserver.lease.period
  120
  

  
  hbase.regionserver.lease.period
  120
  
  
  hbase.rpc.timeout
  120
  
  
  hbase.client.scanner.caching
  1000
  
  
  hbase.client.scanner.timeout.period
  120
  

but with no help. Any suggessation would be great, Mostly I use python
client, but this happens when I use sqlline as well.

Thanks


On Thu, Nov 23, 2017 at 10:30 AM, Vaghawan Ojha <vaghawan...@gmail.com>
wrote:

> Hi,
>
> Thank you very much for reaching out,
>
> - Phoenix version: 4.12.0-HBase-1.2
> - Hbase: 1.2.6
> - Hadoop: 2.8.1
>
>
> DEBUG from PQS:
>
> 2017-11-23 04:27:46,157 WARN org.apache.hadoop.hbase.client.ScannerCallable:
> Ignore, probably already closed
> org.apache.hadoop.hbase.UnknownScannerException: 
> org.apache.hadoop.hbase.UnknownScannerException:
> Unknown scanner '14111'. This can happen due$
> If the issue is due to reason (b), a possible fix would be increasing the
> value of'hbase.client.scanner.timeout.period' configuration.
> at org.apache.hadoop.hbase.regionserver.RSRpcServices.
> scan(RSRpcServices.java:2394)
> at org.apache.hadoop.hbase.protobuf.generated.
> ClientProtos$ClientSer

Queryserver timeouts for the multiple simultaneous Requests

[Vaghawan Ojha]

Hi,

I've a one Namenode server and another Datanode server, phoenix query
server are running on both.

For a single request, everything is fine, but when I do multiple requests
simultaneously, the query server timeouts , the table consists only 2-3 gbs
of data.

I think, I'm missing some configuration to manage this, if somebody could
help me out, it would be great.

Thank you
Vaghawan

Re: Upserting in batch into a column of all rows by concatenating multiple columns

[Vaghawan Ojha]

Thank you, Sorry for my poor reading, how I missed it.

Thank you
Vaghawan

On Sat, Oct 28, 2017 at 11:01 PM, James Taylor <jamestay...@apache.org>
wrote:

> See http://phoenix.apache.org/language/index.html#upsert_select
>
> On Sat, Oct 28, 2017 at 4:25 AM Vaghawan Ojha <vaghawan...@gmail.com>
> wrote:
>
>> Hi,
>>
>> I want to update a column's value in all rows by concatenating values
>> from the multiple columns of the rows. In plain sql it's possible to do
>> that but I'm not sure if that can be done in Phoenix.
>>
>> For say I've four columns in a table:
>>
>> (id, product_id, price, store_id, bill_id)
>>
>> Now I already have the values of id, product_id, price and store_id in
>> the table. But now I want to set the values of bill_id in all rows to
>> something like this:
>>
>> product_id || '-' || store_id
>>
>> Is it possible? If it is, then how do I do it. I don't know if that can
>> be done with Atomic upsert.
>>
>> Any help would be great.
>>
>> Thanks
>> Vaghawan
>>
>

Upserting in batch into a column of all rows by concatenating multiple columns

[Vaghawan Ojha]

Hi,

I want to update a column's value in all rows by concatenating values from
the multiple columns of the rows. In plain sql it's possible to do that but
I'm not sure if that can be done in Phoenix.

For say I've four columns in a table:

(id, product_id, price, store_id, bill_id)

Now I already have the values of id, product_id, price and store_id in the
table. But now I want to set the values of bill_id in all rows to something
like this:

product_id || '-' || store_id

Is it possible? If it is, then how do I do it. I don't know if that can be
done with Atomic upsert.

Any help would be great.

Thanks
Vaghawan

Re: SQLException While Trying To Connect phoenixdb python

[Vaghawan Ojha]

Yes, Thank you for your response, the thin client wasn't working due to the
Hadoop and HBase classpath. I fixed it. I'm actually using python client,
it seems the connection is reset everytime when I finish one operation.

Thanks
Vaghawan

On Tue, Oct 24, 2017 at 6:17 PM, Datta Mane <datta.ma...@gmail.com> wrote:

>
> Check the database URL
>
> This is how JDBC URL is configured
>
> jdbc:phoenix:thin:url=http://ip-10-46-174-227.ec2.internal:
> 8765;serialization=PROTOBUF;authentication=SPNEGO
>
> Make sure you have proper driver files in system path.
>
> On Mon, Oct 23, 2017 at 4:58 AM, Vaghawan Ojha <vaghawan...@gmail.com>
> wrote:
>
>> Hi, the sqline.py works fine in the server console, but when I try to
>> connect it through python, it gives the following SQLException:
>>
>>
>>
>> File "/usr/local/lib/python2.7/dist-packages/phoenixdb/avatica.py", line 
>> 129, in parse_error_protobuf
>> raise errors.InternalError(err.error_message)
>> phoenixdb.errors.InternalError: (u'RuntimeException: java.sql.SQLException: 
>> No suitable driver found for bigdata-namenode:8765 -> SQLException: No 
>> suitable driver found for bigdata-namenode:8765', None, None, None)
>>
>>
>>
>> import phoenixdb
>> database_url = 'http://localhost:8765/'conn = 
>> phoenixdb.connect(database_url, autocommit=True)
>>
>> Can anybody please help me what's wrong, I'm not sure if I need to
>> explicitly give the driver class or something, I'm working with the python.
>>
>> Any help would be great.
>>
>> Thanks
>> Vaghawan
>>
>
>

SQLException While Trying To Connect phoenixdb python

[Vaghawan Ojha]

Hi, the sqline.py works fine in the server console, but when I try to
connect it through python, it gives the following SQLException:



File "/usr/local/lib/python2.7/dist-packages/phoenixdb/avatica.py",
line 129, in parse_error_protobuf
raise errors.InternalError(err.error_message)
phoenixdb.errors.InternalError: (u'RuntimeException:
java.sql.SQLException: No suitable driver found for
bigdata-namenode:8765 -> SQLException: No suitable driver found for
bigdata-namenode:8765', None, None, None)



import phoenixdb
database_url = 'http://localhost:8765/'conn =
phoenixdb.connect(database_url, autocommit=True)

Can anybody please help me what's wrong, I'm not sure if I need to
explicitly give the driver class or something, I'm working with the python.

Any help would be great.

Thanks
Vaghawan