lens-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Pawan Malwal <pmalwal1...@gmail.com>
Subject Re: Trying to add Derby DB as a Lens data source.
Date Fri, 09 Sep 2016 07:24:56 GMT
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>
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>
> 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.HiveIgnoreKeyTextOutputFormat"
>> serde_class_name="org.apache.lens.storage.db.DBSerde"
>> ...
>> ....
>>
>>
>>
>> On Fri, Sep 9, 2016 at 11:35 AM, Rajat Khandelwal <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>
>>> 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.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"
>>>> value="dim_account"/>
>>>>
>>>>                     <property name="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

Mime
View raw message