lens-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Pawan Malwal <pawan.mal...@huawei.com>
Subject RE: Trying to add Derby DB as a Lens data source.
Date Mon, 12 Sep 2016 11:44:32 GMT
Hi Rajat,

I tried running the query on dimension. It gives me below error:

lens-shell>select account_name from account
Request Id: 1891eb0e-5f0a-47ca-a10f-8ba699ec0259
Error Code: 4002
Error Message: Driver Execution Error : A client timed out while waiting to acquire a resource
from com.mchange.v2.resourcepool.BasicResourcePool@5d754b10 -- timeout at awaitAvailable()

Regards
Pawan Malwal

From: Pawan Malwal [mailto:pmalwal1981@gmail.com]
Sent: 09 September 2016 13:03
To: user@lens.apache.org
Cc: Sharanabasappa G Keriwaddi <sharan@huawei.com>
Subject: Re: Trying to add Derby DB as a Lens data source.

Got it. Thanks so much.

Regards
Pawan Malwal

On Fri, Sep 9, 2016 at 12:57 PM, Rajat Khandelwal <rajatgupta59@gmail.com<mailto:rajatgupta59@gmail.com>>
wrote:
Yes, exporting into LENSCPPATH is the way to do this. You can edit lens-env.sh and add the
export line there, or you can rely on bash sessions to have that environment variable.

On Fri, Sep 9, 2016 at 12:55 PM Pawan Malwal <pmalwal1981@gmail.com<mailto:pmalwal1981@gmail.com>>
wrote:
I did it y'day after creating the dimension and I was getting the blank file created in the
output folder. But for all the other dim tables created by run-examples.sh, such queries generates
some data in the output files.
I will do it again and let you know the results by Monday as I do not have access to the server
from home.

Meanwhile if you can please reply to this question. Just to confirm.. :)

Could you please let me know where do we need to have the DB jar files. I am not sure if I
have kept the Derydb jar file at the right place. Actually I just included the .jar file path
in the CLASSPATH variable. Is this the correct way.?

Regards
Pawan Malwal

On Fri, Sep 9, 2016 at 12:35 PM, Rajat Khandelwal <rajatgupta59@gmail.com<mailto:rajatgupta59@gmail.com>>
wrote:
Apologies, I totally missed step 1.

Can you try running a query on dimension? e.g.

select account_name from account



On Fri, Sep 9, 2016 at 12:22 PM Pawan Malwal <pmalwal1981@gmail.com<mailto:pmalwal1981@gmail.com>>
wrote:
Hello Rajat,

Thanks for your reply. Please see my comments inline:
Also could you please let me know where do we need to have the DB jar files. I am not sure
if I have kept the Derydb jar file at the right place. Actually I just included the .jar file
path in the CLASSPATH variable. Is this the correct way.?

1. You haven't created the dimension. You have to create manually.
Pawan> I figured it out later and created the dimension for it.


2. The JDBC Driver has to have derbystorage in its supported storages.
Pawan> I guess. its alreay there as mentioned in Step1 and Step4. Is this what you are
pointing out or there is any other place I need to mention it in supported storages.?

Step1: Created a jdbc driver as follows:

<?xml version="1.0"?>
<?xml-stylesheet type="text/xsl" href="configuration.xsl"?>
.......
.....
  <property>
    <name>lens.cube.query.driver.supported.storages</name>
    <value>derbydb</value>
    <final>true</final>
......
...

....

..

Step 4: Added a dimension table:

<storage_tables>
        <storage_table>
            <storage_name>derbydb</storage_name>
            <table_desc external="true"  input_format="org.apache.hadoop.mapred.TextInputFormat"
output_format="org.apache.hadoop.hive.ql.io<http://hadoop.hive.ql.io>.HiveIgnoreKeyTextOutputFormat"
serde_class_name="org.apache.lens.storage.db.DBSerde"
...
....



On Fri, Sep 9, 2016 at 11:35 AM, Rajat Khandelwal <rajatgupta59@gmail.com<mailto:rajatgupta59@gmail.com>>
wrote:
Couple of things

1. You haven't created the dimension. You have to create manually.
2. The JDBC Driver has to have derbystorage in its supported storages.



On Wed, Sep 7, 2016 at 7:16 PM Pawan Malwal <pawan.malwal@huawei.com<mailto:pawan.malwal@huawei.com>>
wrote:
Hi Lens Experts,

I have started exploring apache Lens recently. I am following Apache docs and some of the
archived mails to analyze its features. I have come across an issue while trying to add Derby
DB as a data source.
Here are the details of what I have done so far. Please excuse me for elaborated mail as I
have put most of configurations to provide all the information as I suspect something wrong
with my configurations here.

Step1: Created a jdbc driver as follows:

<?xml version="1.0"?>
<?xml-stylesheet type="text/xsl" href="configuration.xsl"?>

<configuration>
  <property>
    <name>lens.driver.jdbc.driver.class</name>
    <value>org.apache.derby.jdbc.EmbeddedDriver</value>
  </property>
  <property>
    <name>lens.driver.jdbc.db.uri</name>
    <value>jdbc:derby://localhost:1527/derbyDB;create=true</value>
  </property>
  <property>
    <name>lens.cube.query.driver.supported.storages</name>
    <value>derbydb</value>
    <final>true</final>
  </property>
  <property>
    <name>lens.driver.jdbc.query.rewriter</name>
    <value>org.apache.lens.driver.jdbc.ColumnarSQLRewriter</value>
  </property>
  <property>
    <name>lens.driver.jdbc.explain.keyword</name>
    <value>explain plan for </value>
  </property>
</configuration>

Step2: Created table in Derby DB:

ij> connect 'jdbc:derby:derbyDB';

ij> create table DIM_ACCOUNT(id int,account_name varchar(40),status varchar(40),enabled
int);
0 rows inserted/updated/deleted

ij> insert into DIM_ACCOUNT values(1,'account1','active',1);
1 row inserted/updated/deleted

ij> insert into DIM_ACCOUNT values(2,'account2','Inactive',1);
1 row inserted/updated/deleted

ij> select * from DIM_ACCOUNT;
ID         |ACCOUNT_NAME                            |STATUS                              
   |ENABLED
---------------------------------------------------------------------------------------------------------
1          |account1                                |active                              
   |1
2          |account2                                |Inactive                            
   |1

2 rows selected
ij>

Step 3: Created the storage file:

lens-shell>! grep -A 33333333 <x_ derby-db-storage.xml
command is:grep -A 33333333 <x_ derby-db-storage.xml
<x_storage classname="org.apache.lens.storage.db.DBStorage" name="derbydb" xmlns="uri:lens:cube:0.1"
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="uri:lens:cube:0.1
cube-0.1.xsd ">
  <properties>
    <property name="lens.storage.db.url" value="jdbc:derby://localhost:1527/derbyDB"/>
  </properties>
</x_storage>
lens-shell>create storage examples/resources/derby-db-storage.xml
succeeded

Step 4: Added a dimension table:

lens-shell>! grep -A 33333333 <x_ examples/resources/derby_account_table.xml
command is:grep -A 33333333 <x_ examples/resources/derby_account_table.xml
<x_dimension_table xmlns="uri:lens:cube:0.1" dimension_name="account" table_name="account_derby"
weight="0.1">
    <columns>
        <column name="id" _type="int" comment=""/>
        <column name="account_name" _type="string" comment=""/>
        <column name="status" _type="string" comment=""/>
        <column name="enabled" _type="boolean" comment=""/>
    </columns>
    <storage_tables>
        <storage_table>
            <storage_name>derbydb</storage_name>
            <table_desc external="true"  input_format="org.apache.hadoop.mapred.TextInputFormat"
output_format="org.apache.hadoop.hive.ql.io<http://hadoop.hive.ql.io>.HiveIgnoreKeyTextOutputFormat"
serde_class_name="org.apache.lens.storage.db.DBSerde" storage_handler_name="org.apache.lens.storage.db.DBStorageHandler"
 >
                <table_parameters>
                    <property name="lens.metastore.native.table.name<http://lens.metastore.native.table.name>"
value="dim_account"/>
                    <property name="lens.metastore.native.db.name<http://lens.metastore.native.db.name>"
value="derbyDB"/>
                </table_parameters>
            </table_desc>
        </storage_table>
    </storage_tables>
</x_dimension_table>

lens-shell>create dimtable examples/resources/derby_account_table.xml
succeeded

-------------------------------------------------------------------------------------------------------------------------------------------------------

Now, when I am trying to run a query from lens-shell, I am getting below error. Also I do
not see “account” dimension created above using show dimensions command. Not sure if I
did anything wrong or missed something.


lens-shell>show storages
cluster1
derbydb
local
mydb

lens-shell>select * from DIM_ACCOUNT
Request Id: bc846148-e585-43b5-9483-01f2e5c28ea1
Error Code: 4001
Error Message: Semantic Error : user lacks privilege or object not found: DIM_ACCOUNT


lens-shell>show dimensions
product
sample_dim2
city
sample_db_dim
sample_dim
customer


Regards
Pawan Malwal






--
Thanks and Regards
Pawan Malwal



--
Thanks and Regards
Pawan Malwal



--
Thanks and Regards
Pawan Malwal
Mime
View raw message