lens-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rajat Khandelwal <rajatgupt...@gmail.com>
Subject Re: Trying to add Derby DB as a Lens data source.
Date Fri, 09 Sep 2016 07:05:46 GMT
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
>

Mime
View raw message