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