lens-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rajat Khandelwal <rajatgupt...@gmail.com>
Subject Re: Adding MySQL as a Data Source
Date Mon, 29 Aug 2016 07:46:33 GMT
Use something like

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<x_storage xmlns="uri:lens:cube:0.1" name="mysql"
classname="org.apache.lens.storage.db.DBStorage">
</x_storage>


<storage_table>
  <update_periods>
    <update_period>HOURLY</update_period>
  </update_periods>
  <storage_name>db</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"
num_buckets="0" compressed="false">
    <part_cols>
      <column name="pt" _type="string" comment="date partition"/>
      <column name="dt" _type="string" comment="date partition"/>
      <column name="ot" _type="string" comment="date partition"/>
    </part_cols>
    <table_parameters>
      <property name="lens.metastore.native.table.name"
value="table_name_in_mysql_db"/>
      <property name="lens.metastore.native.db.name" value="mysql_db_name"/>
      <property name="lens.metastore.native.table.column.mapping"

value="pt=process_time,field1=field1_actual_name,field2=field2_actual_name"/>
      <property name="cube.storagetable.start.times" value="now - 4 days"/>
    </table_parameters>
    <time_part_cols>pt</time_part_cols>
    <time_part_cols>dt</time_part_cols>
    <time_part_cols>ot</time_part_cols>
  </table_desc>
</storage_table>


On Sat, Aug 27, 2016 at 3:42 AM Tao Yan <tyan@linkedin.com> wrote:

> Hi Lens Developers,
>
> I am trying to add mysql as a data source, so, I created the driver as
> follows:
>
> <?xml-stylesheet type="text/xsl" href="configuration.xsl"?>
>
> <configuration>
>   <property>
>     <name>lens.driver.jdbc.driver.class</name>
>     <value>com.mysql.jdbc.Driver</value>
>   </property>
>   <property>
>     <name>lens.driver.jdbc.db.uri</name>
>
> <value>jdbc:mysql://****mysql**hostname****/lens;user=****;passowrd=****</value>
>   </property>
>   <property>
>     <name>lens.driver.jdbc.db.user</name>
>     <value>****</value>
>   </property>
>   <property>
>     <name>lens.cube.query.driver.supported.storages</name>
>     <value>mysql</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>
>
> And created a test table with data in mysql:
>
> create table dimension1_subset (
> primary_key BIGINT(8) not null primary key,
> attr1 BIGINT(8),
> attr5 BIGINT(8)
> );
>
> [image: Screen Shot 2016-08-26 at 2.44.24 PM.png]
> ‚Äč
> And created the storage file:
>
> <?xml version="1.0" encoding="UTF-8"?>
>
> <x_storage classname="org.apache.lens.storage.db.DBStorage" name="mysql"
> 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:mysql://lva1-db34.corp.linkedin.com/lens
> <http://lva1-db34.corp.linkedin.com/lens>"/>*
>   </properties>
> </x_storage>
>
> I am confused when set the value for *lens.storage.db.url *because* it is
> neither local filesystem nor HDFS.*
>
> And I don't know what should the storage table look like:
>
> <x_dimension_table dimension_name="dimension1"
> table_name="dimension1_table3" weight="5.0" 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 ">
>   <columns>
>     <column name="primary_key" _type="BIGINT"/>
>     <column name="attr1" _type="BIGINT"/>
>     <column name="attr5" _type="BIGINT"/>
>   </columns>
>   <properties>
>     <property name="dimension1.prop" value="t3"/>
>   </properties>
>   <storage_tables>
>
>
> *    <storage_table>      ...    </storage_table>*
>   </storage_tables>
> </x_dimension_table>
>
> What should I use for the table_location, and what about part_cols? How do
> I add data to this table?
>
> Thanks,
> --
>
> *Tao Yan*
> Software Engineer
> Data Analytics Infrastructure Tools and Services
>
>
>
> 206.250.5345
> tyan@linkedin.com
> https://www.linkedin.com/in/taousc
>

Mime
View raw message