Tao, hive view with two dimension joins may not work here, as there is no such support. It would work for other storages.
Thanks On Thu, Sep 8, 2016 at 10:49 AM, Puneet Gupta <puneet.gu...@inmobi.com> wrote: > +Amareshwari > > Thanks, > Puneet Gupta > > On Thu, Sep 1, 2016 at 10:16 PM, Tao Yan <t...@linkedin.com> wrote: > >> If the two division tables are on HDFS or in Hive and I create a hive >> view which joins two divisions, can I use the view as lens dimension table? >> >> Thanks! >> >> On Thu, Sep 1, 2016 at 12:59 AM, Puneet Gupta <puneet.gu...@inmobi.com> >> wrote: >> >>> We currently don't see the need to support dimension table sub >>> divisions. Thats why this jira is parked since a long time. >>> >>> You can do the following >>> 1. Create two dimensions and two dimension tables. Dimension to >>> Dimension joins can be mentioned in join chains >>> or >>> 2.Create a view in MYSQL which you join the two sub divisions . Use the >>> view as lens dimension table instead. >>> >>> Thanks, >>> Puneet Gupta >>> >>> On Fri, Aug 26, 2016 at 10:33 PM, Tao Yan <t...@linkedin.com> wrote: >>> >>>> Any plan to support this feature in the next version? >>>> >>>> Thanks, >>>> >>>> On Fri, Aug 26, 2016 at 3:41 AM, Rajat Khandelwal < >>>> rajatgupt...@gmail.com> wrote: >>>> >>>>> Seems you are hitting a known issue. https://issues.apache.o >>>>> rg/jira/browse/LENS-453 >>>>> The feature is not supported in the current version. >>>>> >>>>> >>>>> On Fri, Aug 26, 2016 at 3:20 AM Tao Yan <t...@linkedin.com> wrote: >>>>> >>>>>> Hi Lens Developers, >>>>>> >>>>>> I am trying to define a dimension as follows: >>>>>> >>>>>> *dimension1.xml:* >>>>>> *<x_dimension name="dimension1" xmlns="uri:lens:cube:0.1" >>>>>> xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance >>>>>> <http://www.w3.org/2001/XMLSchema-instance>"* >>>>>> * xsi:schemaLocation="uri:lens:cube:0.1 cube-0.1.xsd ">* >>>>>> * <attributes>* >>>>>> * <dim_attribute name="primary_key" _type="BIGINT"/>* >>>>>> * <dim_attribute name="attr1" _type="BIGINT"/>* >>>>>> * <dim_attribute name="attr2" _type="BIGINT"/>* >>>>>> * <dim_attribute name="attr3" _type="BIGINT"/>* >>>>>> * <dim_attribute name="attr4" _type="BIGINT"/>* >>>>>> * <dim_attribute name="attr5" _type="BIGINT"/>* >>>>>> * <dim_attribute name="attr6" _type="BIGINT"/>* >>>>>> * <dim_attribute name="attr7" _type="BIGINT"/>* >>>>>> * <dim_attribute name="attr8" _type="BIGINT"/>* >>>>>> * </attributes>* >>>>>> * <properties>* >>>>>> * <property name="dimension.dimension1.timed.dimension" >>>>>> value="dt"/>* >>>>>> * </properties>* >>>>>> *</x_dimension>* >>>>>> >>>>>> And defined two dimtables under this dimension: >>>>>> >>>>>> *dimension1_division1.xml* >>>>>> <x_dimension_table dimension_name="dimension1" >>>>>> table_name="dimension1_division1" weight="10.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="attr2" _type="BIGINT"/>* >>>>>> * <column name="attr3" _type="BIGINT"/>* >>>>>> * <column name="attr4" _type="BIGINT"/>* >>>>>> </columns> >>>>>> <properties> >>>>>> <property name="dimension1.prop" value="d1"/> >>>>>> </properties> >>>>>> <storage_tables> >>>>>> ... >>>>>> </storage_tables> >>>>>> </x_dimension_table> >>>>>> >>>>>> >>>>>> *dimension1_division2.xml* >>>>>> <x_dimension_table dimension_name="dimension1" >>>>>> table_name="dimension1_division2" weight="10.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="attr5" _type="BIGINT"/>* >>>>>> * <column name="attr6" _type="BIGINT"/>* >>>>>> * <column name="attr7" _type="BIGINT"/>* >>>>>> * <column name="attr8" _type="BIGINT"/>* >>>>>> </columns> >>>>>> <properties> >>>>>> <property name="dimension1.prop" value="d2"/> >>>>>> </properties> >>>>>> <storage_tables> >>>>>> ... >>>>>> </storage_tables> >>>>>> </x_dimension_table> >>>>>> >>>>>> And added some sample data for both dimtables. When I run queries >>>>>> like: >>>>>> *cube select primary_key, attr1 from dimension1* >>>>>> OR >>>>>> *cube select primary_key, attr5 from dimension1* >>>>>> It returns result correctly, however, when I run the following query: >>>>>> >>>>>> l*ens-shell>cube select primary_key, attr1, attr5 from dimension1* >>>>>> It gives the following error: >>>>>> Request Id: 80676c43-e3e8-455b-b3da-4d38b9f093d2 >>>>>> Error Code: 3023 >>>>>> Error Message: No dimension table has the queried columns for >>>>>> dimension1, columns: [primary_key, attr1, attr5] >>>>>> >>>>>> I expect Lens will figure out attr1 and attr5 is not in the same >>>>>> logical table, but can be obtained by joining logical tables >>>>>> 'dimension1_division1' and 'dimension1_division2' on 'primary_key', so, I >>>>>> also tried to define primary key as join key: >>>>>> >>>>>> *<dim_attribute name="primary_key" _type="BIGINT" join_key="true"/>* >>>>>> >>>>>> And updating the dimension failed with no error logs at all: >>>>>> >>>>>> *lens-shell>update dimension --name dimension1 --path >>>>>> /export/home/dev_svc/lens/schemas/dimension1.xml* >>>>>> *failed* >>>>>> >>>>>> Is it possible to achieve this goal, or Lens currently does not >>>>>> support it? >>>>>> >>>>>> Thanks, >>>>>> -- >>>>>> >>>>>> *Tao Yan* >>>>>> Software Engineer >>>>>> Data Analytics Infrastructure Tools and Services >>>>>> >>>>>> >>>>>> >>>>>> 206.250.5345 >>>>>> t...@linkedin.com >>>>>> https://www.linkedin.com/in/taousc >>>>>> >>>>> >>>> >>>> >>>> -- >>>> >>>> *Tao Yan* >>>> Software Engineer >>>> Data Analytics Infrastructure Tools and Services >>>> >>>> >>>> >>>> 206.250.5345 >>>> t...@linkedin.com >>>> https://www.linkedin.com/in/taousc >>>> >>> >>> >>> _____________________________________________________________ >>> The information contained in this communication is intended solely for >>> the use of the individual or entity to whom it is addressed and others >>> authorized to receive it. It may contain confidential or legally privileged >>> information. If you are not the intended recipient you are hereby notified >>> that any disclosure, copying, distribution or taking any action in reliance >>> on the contents of this information is strictly prohibited and may be >>> unlawful. If you have received this communication in error, please notify >>> us immediately by responding to this email and then delete it from your >>> system. The firm is neither liable for the proper and complete transmission >>> of the information contained in this communication nor for any delay in its >>> receipt. >> >> >> >> >> -- >> >> *Tao Yan* >> Software Engineer >> Data Analytics Infrastructure Tools and Services >> >> >> >> 206.250.5345 >> t...@linkedin.com >> https://www.linkedin.com/in/taousc >> > > > _____________________________________________________________ > The information contained in this communication is intended solely for the > use of the individual or entity to whom it is addressed and others > authorized to receive it. It may contain confidential or legally privileged > information. If you are not the intended recipient you are hereby notified > that any disclosure, copying, distribution or taking any action in reliance > on the contents of this information is strictly prohibited and may be > unlawful. If you have received this communication in error, please notify > us immediately by responding to this email and then delete it from your > system. The firm is neither liable for the proper and complete transmission > of the information contained in this communication nor for any delay in its > receipt. >
