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:33:26 GMT
Got it. Thanks so much.

Regards
Pawan Malwal

On Fri, Sep 9, 2016 at 12:57 PM, Rajat Khandelwal <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>
> 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
>> > 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
>>
>


-- 
Thanks and Regards
Pawan Malwal

Mime
View raw message