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 06:51:51 GMT
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