[
https://issues.apache.org/jira/browse/IGNITE-10859?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Ayush updated IGNITE-10859:
---------------------------
Description:
When we are loading two data-frames from ignite in spark and joining those to
dataframes, it is giving exception. We have checked the generated logical plan
and seems like it is wrong.
I am adding the stack trace and code
scala> val df1 = spark.read.format(FORMAT_IGNITE).option(OPTION_CONFIG_FILE,
CONFIG).option(OPTION_TABLE,
"HIVE_customer_address_2_1546577865912").load().toDF(schema1.columns
map(_.toLowerCase): _*)
df1: org.apache.spark.sql.DataFrame = [ca_address_sk: int, ca_address_id:
string ... 11 more fields]
scala> df1.show(1)
+----------------+-------------++++------------------------------++-----------------------------++------------------------++--------------++------------------------------------------
|ca_address_sk|ca_address_id|ca_street_number|ca_street_name|ca_street_type|ca_suite_number|ca_city|ca_county|ca_state|ca_zip|ca_country|ca_gmt_offset|ca_location_type|
+----------------+-------------++++------------------------------++-----------------------------++------------------------++--------------++------------------------------------------
|1|AAAAAAAABAAAAAAA|18|Jackson|Parkway|Suite 280|Fairfield|Maricopa
County|AZ|86192|United States|-7.00|condo|
+----------------+-------------++++------------------------------++-----------------------------++------------------------++--------------++------------------------------------------
only showing top 1 row
scala> val df2 = spark.read.format(FORMAT_IGNITE).option(OPTION_CONFIG_FILE,
CONFIG).option(OPTION_TABLE,
"POSTGRES_customer_1_1546598025406").load().toDF(schema2.columns
map(_.toLowerCase): _*)
df2: org.apache.spark.sql.DataFrame] = [c_customer_sk: int, c_customer_id:
string ... 16 more fields]
scala> df2.show(1)
+----------------+-------------++++------------------------------------++---------------------------------------++---------------------------------++----------------------------------------++--------------------------------++-------------------------++----------------------+--------------------------------------
|c_customer_sk|c_customer_id|c_current_cdemo_sk|c_current_hdemo_sk|c_current_addr_sk|c_first_shipto_date_sk|c_first_sales_date_sk|c_salutation|c_first_name|c_last_name|c_preferred_cust_flag|c_birth_day|c_birth_month|c_birth_year|c_birth_country|c_login|c_email_address|c_last_review_date|
+----------------+-------------++++------------------------------------++---------------------------------------++---------------------------------++----------------------------------------++--------------------------------++-------------------------++----------------------+--------------------------------------
|7288|AAAAAAAAIHMBAAAA|1461725|4938|18198|2450838|2450808|Sir|Steven|Storey
...|Y|1|2|1967|QATAR|null|Steven.Storey@QdG...|2452528|
+----------------+-------------++++------------------------------------++---------------------------------------++---------------------------------++----------------------------------------++--------------------------------++-------------------------++----------------------+--------------------------------------
scala> df1.join(df2, df1.col("ca_address_sk") === df2.col("c_customer_sk"),
"inner")
res64: org.apache.spark.sql.DataFrame = [ca_address_sk: int, ca_address_id:
string ... 29 more fields]
scala> res64.show
19/01/04 16:50:07 ERROR Executor: Exception in task 0.0 in stage 15.0 (TID 15)
javax.cache.CacheException: Failed to parse query. Column
"POSTGRES_CUSTOMER_1_1546598025406.CA_ADDRESS_SK" not found; SQL statement:
SELECT CAST(HIVE_customer_address_2_1546577865912.CA_ADDRESS_SK AS VARCHAR) AS
ca_address_sk, HIVE_customer_address_2_1546577865912.CA_ADDRESS_ID,
HIVE_customer_address_2_1546577865912.CA_STREET_NUMBER,
HIVE_customer_address_2_1546577865912.CA_STREET_NAME,
HIVE_customer_address_2_1546577865912.CA_STREET_TYPE,
HIVE_customer_address_2_1546577865912.CA_SUITE_NUMBER,
HIVE_customer_address_2_1546577865912.CA_CITY,
HIVE_customer_address_2_1546577865912.CA_COUNTY,
HIVE_customer_address_2_1546577865912.CA_STATE,
HIVE_customer_address_2_1546577865912.CA_ZIP,
HIVE_customer_address_2_1546577865912.CA_COUNTRY,
CAST(HIVE_customer_address_2_1546577865912.CA_GMT_OFFSET AS VARCHAR) AS
ca_gmt_offset, HIVE_customer_address_2_1546577865912.CA_LOCATION_TYPE,
CAST(POSTGRES_customer_1_1546598025406.C_CUSTOMER_SK AS VARCHAR) AS
c_customer_sk, POSTGRES_customer_1_1546598025406.C_CUSTOMER_ID,
CAST(POSTGRES_customer_1_1546598025406.C_CURRENT_CDEMO_SK AS VARCHAR) AS
c_current_cdemo_sk, CAST(POSTGRES_customer_1_1546598025406.C_CURRENT_HDEMO_SK
AS VARCHAR) AS c_current_hdemo_sk,
CAST(POSTGRES_customer_1_1546598025406.C_CURRENT_ADDR_SK AS VARCHAR) AS
c_current_addr_sk,
CAST(POSTGRES_customer_1_1546598025406.C_FIRST_SHIPTO_DATE_SK AS VARCHAR) AS
c_first_shipto_date_sk,
CAST(POSTGRES_customer_1_1546598025406.C_FIRST_SALES_DATE_SK AS VARCHAR) AS
c_first_sales_date_sk, POSTGRES_customer_1_1546598025406.C_SALUTATION,
POSTGRES_customer_1_1546598025406.C_FIRST_NAME,
POSTGRES_customer_1_1546598025406.C_LAST_NAME,
POSTGRES_customer_1_1546598025406.C_PREFERRED_CUST_FLAG,
CAST(POSTGRES_customer_1_1546598025406.C_BIRTH_DAY AS VARCHAR) AS c_birth_day,
CAST(POSTGRES_customer_1_1546598025406.C_BIRTH_MONTH AS VARCHAR) AS
c_birth_month, CAST(POSTGRES_customer_1_1546598025406.C_BIRTH_YEAR AS VARCHAR)
AS c_birth_year, POSTGRES_customer_1_1546598025406.C_BIRTH_COUNTRY,
POSTGRES_customer_1_1546598025406.C_LOGIN,
POSTGRES_customer_1_1546598025406.C_EMAIL_ADDRESS,
POSTGRES_customer_1_1546598025406.C_LAST_REVIEW_DATE FROM
HIVE_customer_address_2_1546577865912 JOIN POSTGRES_customer_1_1546598025406 ON
HIVE_customer_address_2_1546577865912.ca_address_sk =
POSTGRES_customer_1_1546598025406.c_customer_sk WHERE
POSTGRES_customer_1_1546598025406.CA_ADDRESS_SK IS NOT NULL AND
POSTGRES_customer_1_1546598025406.C_CUSTOMER_SK IS NOT NULL LIMIT 21 [42122-195]
at
org.apache.ignite.internal.processors.cache.IgniteCacheProxyImpl.query(IgniteCacheProxyImpl.java:676)
at
org.apache.ignite.internal.processors.cache.GatewayProtectedCacheProxy.query(GatewayProtectedCacheProxy.java:344)
at org.apache.ignite.spark.impl.IgniteSqlRDD.compute(IgniteSqlRDD.scala:37)
at
org.apache.ignite.spark.impl.IgniteSQLDataFrameRDD.compute(IgniteSQLDataFrameRDD.scala:74)
at org.apache.spark.rdd.RDD.computeOrReadCheckpoint(RDD.scala:324)
at org.apache.spark.rdd.RDD.iterator(RDD.scala:288)
at org.apache.spark.rdd.MapPartitionsRDD.compute(MapPartitionsRDD.scala:38)
at org.apache.spark.rdd.RDD.computeOrReadCheckpoint(RDD.scala:324)
at org.apache.spark.rdd.RDD.iterator(RDD.scala:288)
at org.apache.spark.rdd.MapPartitionsRDD.compute(MapPartitionsRDD.scala:38)
at org.apache.spark.rdd.RDD.computeOrReadCheckpoint(RDD.scala:324)
at org.apache.spark.rdd.RDD.iterator(RDD.scala:288)
at org.apache.spark.rdd.MapPartitionsRDD.compute(MapPartitionsRDD.scala:38)
at org.apache.spark.rdd.RDD.computeOrReadCheckpoint(RDD.scala:324)
at org.apache.spark.rdd.RDD.iterator(RDD.scala:288)
at org.apache.spark.scheduler.ResultTask.runTask(ResultTask.scala:87)
at org.apache.spark.scheduler.Task.run(Task.scala:109)
at org.apache.spark.executor.Executor$TaskRunner.run(Executor.scala:345)
at
java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
at
java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
at java.lang.Thread.run(Thread.java:745)
//Generated Plan
scala> res67.explain(true)
== Parsed Logical Plan ==
Join Inner, (ca_address_sk#2399 = c_customer_sk#2519)
:- Project [ca_address_sk#2399, ca_address_id#2400, ca_street_number#2401,
ca_street_name#2402, ca_street_type#2403, ca_suite_number#2404, ca_city#2405,
ca_county#2406, ca_state#2407, ca_zip#2408, ca_country#2409,
ca_gmt_offset#2410, ca_location_type#2411|#2399, ca_address_id#2400,
ca_street_number#2401, ca_street_name#2402, ca_street_type#2403,
ca_suite_number#2404, ca_city#2405, ca_county#2406, ca_state#2407, ca_zip#2408,
ca_country#2409, ca_gmt_offset#2410, ca_location_type#2411]
: +- Project [CA_ADDRESS_SK#2371 AS ca_address_sk#2399, CA_ADDRESS_ID#2372 AS
ca_address_id#2400, CA_STREET_NUMBER#2373 AS ca_street_number#2401,
CA_STREET_NAME#2374 AS ca_street_name#2402, CA_STREET_TYPE#2375 AS
ca_street_type#2403, CA_SUITE_NUMBER#2376 AS ca_suite_number#2404, CA_CITY#2377
AS ca_city#2405, CA_COUNTY#2378 AS ca_county#2406, CA_STATE#2379 AS
ca_state#2407, CA_ZIP#2380 AS ca_zip#2408, CA_COUNTRY#2381 AS ca_country#2409,
CA_GMT_OFFSET#2382 AS ca_gmt_offset#2410, CA_LOCATION_TYPE#2383 AS
ca_location_type#2411, IGNITE_INDEX#2384L AS ignite_index#2412L|#2371 AS
ca_address_sk#2399, CA_ADDRESS_ID#2372 AS ca_address_id#2400,
CA_STREET_NUMBER#2373 AS ca_street_number#2401, CA_STREET_NAME#2374 AS
ca_street_name#2402, CA_STREET_TYPE#2375 AS ca_street_type#2403,
CA_SUITE_NUMBER#2376 AS ca_suite_number#2404, CA_CITY#2377 AS ca_city#2405,
CA_COUNTY#2378 AS ca_county#2406, CA_STATE#2379 AS ca_state#2407, CA_ZIP#2380
AS ca_zip#2408, CA_COUNTRY#2381 AS ca_country#2409, CA_GMT_OFFSET#2382 AS
ca_gmt_offset#2410, CA_LOCATION_TYPE#2383 AS ca_location_type#2411,
IGNITE_INDEX#2384L AS ignite_index#2412L]
: +-
Relation[CA_ADDRESS_SK#2371,CA_ADDRESS_ID#2372,CA_STREET_NUMBER#2373,CA_STREET_NAME#2374,CA_STREET_TYPE#2375,CA_SUITE_NUMBER#2376,CA_CITY#2377,CA_COUNTY#2378,CA_STATE#2379,CA_ZIP#2380,CA_COUNTRY#2381,CA_GMT_OFFSET#2382,CA_LOCATION_TYPE#2383,IGNITE_INDEX#2384L|#2371,CA_ADDRESS_ID#2372,CA_STREET_NUMBER#2373,CA_STREET_NAME#2374,CA_STREET_TYPE#2375,CA_SUITE_NUMBER#2376,CA_CITY#2377,CA_COUNTY#2378,CA_STATE#2379,CA_ZIP#2380,CA_COUNTRY#2381,CA_GMT_OFFSET#2382,CA_LOCATION_TYPE#2383,IGNITE_INDEX#2384L]
IgniteSQLRelation[table=HIVE_customer_address_2_1546577865912]
+- Project [c_customer_sk#2519, c_customer_id#2520, c_current_cdemo_sk#2521,
c_current_hdemo_sk#2522, c_current_addr_sk#2523, c_first_shipto_date_sk#2524,
c_first_sales_date_sk#2525, c_salutation#2526, c_first_name#2527,
c_last_name#2528, c_preferred_cust_flag#2529, c_birth_day#2530,
c_birth_month#2531, c_birth_year#2532, c_birth_country#2533, c_login#2534,
c_email_address#2535, c_last_review_date#2536|#2519, c_customer_id#2520,
c_current_cdemo_sk#2521, c_current_hdemo_sk#2522, c_current_addr_sk#2523,
c_first_shipto_date_sk#2524, c_first_sales_date_sk#2525, c_salutation#2526,
c_first_name#2527, c_last_name#2528, c_preferred_cust_flag#2529,
c_birth_day#2530, c_birth_month#2531, c_birth_year#2532, c_birth_country#2533,
c_login#2534, c_email_address#2535, c_last_review_date#2536]
+- Project [C_CUSTOMER_SK#2481 AS c_customer_sk#2519, C_CUSTOMER_ID#2482 AS
c_customer_id#2520, C_CURRENT_CDEMO_SK#2483 AS c_current_cdemo_sk#2521,
C_CURRENT_HDEMO_SK#2484 AS c_current_hdemo_sk#2522, C_CURRENT_ADDR_SK#2485 AS
c_current_addr_sk#2523, C_FIRST_SHIPTO_DATE_SK#2486 AS
c_first_shipto_date_sk#2524, C_FIRST_SALES_DATE_SK#2487 AS
c_first_sales_date_sk#2525, C_SALUTATION#2488 AS c_salutation#2526,
C_FIRST_NAME#2489 AS c_first_name#2527, C_LAST_NAME#2490 AS c_last_name#2528,
C_PREFERRED_CUST_FLAG#2491 AS c_preferred_cust_flag#2529, C_BIRTH_DAY#2492 AS
c_birth_day#2530, C_BIRTH_MONTH#2493 AS c_birth_month#2531, C_BIRTH_YEAR#2494
AS c_birth_year#2532, C_BIRTH_COUNTRY#2495 AS c_birth_country#2533,
C_LOGIN#2496 AS c_login#2534, C_EMAIL_ADDRESS#2497 AS c_email_address#2535,
C_LAST_REVIEW_DATE#2498 AS c_last_review_date#2536, IGNITE_INDEX#2499L AS
ignite_index#2537L|#2481 AS c_customer_sk#2519, C_CUSTOMER_ID#2482 AS
c_customer_id#2520, C_CURRENT_CDEMO_SK#2483 AS c_current_cdemo_sk#2521,
C_CURRENT_HDEMO_SK#2484 AS c_current_hdemo_sk#2522, C_CURRENT_ADDR_SK#2485 AS
c_current_addr_sk#2523, C_FIRST_SHIPTO_DATE_SK#2486 AS
c_first_shipto_date_sk#2524, C_FIRST_SALES_DATE_SK#2487 AS
c_first_sales_date_sk#2525, C_SALUTATION#2488 AS c_salutation#2526,
C_FIRST_NAME#2489 AS c_first_name#2527, C_LAST_NAME#2490 AS c_last_name#2528,
C_PREFERRED_CUST_FLAG#2491 AS c_preferred_cust_flag#2529, C_BIRTH_DAY#2492 AS
c_birth_day#2530, C_BIRTH_MONTH#2493 AS c_birth_month#2531, C_BIRTH_YEAR#2494
AS c_birth_year#2532, C_BIRTH_COUNTRY#2495 AS c_birth_country#2533,
C_LOGIN#2496 AS c_login#2534, C_EMAIL_ADDRESS#2497 AS c_email_address#2535,
C_LAST_REVIEW_DATE#2498 AS c_last_review_date#2536, IGNITE_INDEX#2499L AS
ignite_index#2537L]
+-
Relation[C_CUSTOMER_SK#2481,C_CUSTOMER_ID#2482,C_CURRENT_CDEMO_SK#2483,C_CURRENT_HDEMO_SK#2484,C_CURRENT_ADDR_SK#2485,C_FIRST_SHIPTO_DATE_SK#2486,C_FIRST_SALES_DATE_SK#2487,C_SALUTATION#2488,C_FIRST_NAME#2489,C_LAST_NAME#2490,C_PREFERRED_CUST_FLAG#2491,C_BIRTH_DAY#2492,C_BIRTH_MONTH#2493,C_BIRTH_YEAR#2494,C_BIRTH_COUNTRY#2495,C_LOGIN#2496,C_EMAIL_ADDRESS#2497,C_LAST_REVIEW_DATE#2498,IGNITE_INDEX#2499L|#2481,C_CUSTOMER_ID#2482,C_CURRENT_CDEMO_SK#2483,C_CURRENT_HDEMO_SK#2484,C_CURRENT_ADDR_SK#2485,C_FIRST_SHIPTO_DATE_SK#2486,C_FIRST_SALES_DATE_SK#2487,C_SALUTATION#2488,C_FIRST_NAME#2489,C_LAST_NAME#2490,C_PREFERRED_CUST_FLAG#2491,C_BIRTH_DAY#2492,C_BIRTH_MONTH#2493,C_BIRTH_YEAR#2494,C_BIRTH_COUNTRY#2495,C_LOGIN#2496,C_EMAIL_ADDRESS#2497,C_LAST_REVIEW_DATE#2498,IGNITE_INDEX#2499L]
IgniteSQLRelation[table=POSTGRES_customer_1_1546598025406]
== Analyzed Logical Plan ==
ca_address_sk: int, ca_address_id: string, ca_street_number: string,
ca_street_name: string, ca_street_type: string, ca_suite_number: string,
ca_city: string, ca_county: string, ca_state: string, ca_zip: string,
ca_country: string, ca_gmt_offset: decimal(38,3), ca_location_type: string,
c_customer_sk: int, c_customer_id: string, c_current_cdemo_sk: int,
c_current_hdemo_sk: int, c_current_addr_sk: int, c_first_shipto_date_sk: int,
c_first_sales_date_sk: int, c_salutation: string, c_first_name: string,
c_last_name: string, c_preferred_cust_flag: string, ... 7 more fields
Join Inner, (ca_address_sk#2399 = c_customer_sk#2519)
:- Project [ca_address_sk#2399, ca_address_id#2400, ca_street_number#2401,
ca_street_name#2402, ca_street_type#2403, ca_suite_number#2404, ca_city#2405,
ca_county#2406, ca_state#2407, ca_zip#2408, ca_country#2409,
ca_gmt_offset#2410, ca_location_type#2411|#2399, ca_address_id#2400,
ca_street_number#2401, ca_street_name#2402, ca_street_type#2403,
ca_suite_number#2404, ca_city#2405, ca_county#2406, ca_state#2407, ca_zip#2408,
ca_country#2409, ca_gmt_offset#2410, ca_location_type#2411]
: +- Project [CA_ADDRESS_SK#2371 AS ca_address_sk#2399, CA_ADDRESS_ID#2372 AS
ca_address_id#2400, CA_STREET_NUMBER#2373 AS ca_street_number#2401,
CA_STREET_NAME#2374 AS ca_street_name#2402, CA_STREET_TYPE#2375 AS
ca_street_type#2403, CA_SUITE_NUMBER#2376 AS ca_suite_number#2404, CA_CITY#2377
AS ca_city#2405, CA_COUNTY#2378 AS ca_county#2406, CA_STATE#2379 AS
ca_state#2407, CA_ZIP#2380 AS ca_zip#2408, CA_COUNTRY#2381 AS ca_country#2409,
CA_GMT_OFFSET#2382 AS ca_gmt_offset#2410, CA_LOCATION_TYPE#2383 AS
ca_location_type#2411, IGNITE_INDEX#2384L AS ignite_index#2412L|#2371 AS
ca_address_sk#2399, CA_ADDRESS_ID#2372 AS ca_address_id#2400,
CA_STREET_NUMBER#2373 AS ca_street_number#2401, CA_STREET_NAME#2374 AS
ca_street_name#2402, CA_STREET_TYPE#2375 AS ca_street_type#2403,
CA_SUITE_NUMBER#2376 AS ca_suite_number#2404, CA_CITY#2377 AS ca_city#2405,
CA_COUNTY#2378 AS ca_county#2406, CA_STATE#2379 AS ca_state#2407, CA_ZIP#2380
AS ca_zip#2408, CA_COUNTRY#2381 AS ca_country#2409, CA_GMT_OFFSET#2382 AS
ca_gmt_offset#2410, CA_LOCATION_TYPE#2383 AS ca_location_type#2411,
IGNITE_INDEX#2384L AS ignite_index#2412L]
: +-
Relation[CA_ADDRESS_SK#2371,CA_ADDRESS_ID#2372,CA_STREET_NUMBER#2373,CA_STREET_NAME#2374,CA_STREET_TYPE#2375,CA_SUITE_NUMBER#2376,CA_CITY#2377,CA_COUNTY#2378,CA_STATE#2379,CA_ZIP#2380,CA_COUNTRY#2381,CA_GMT_OFFSET#2382,CA_LOCATION_TYPE#2383,IGNITE_INDEX#2384L|#2371,CA_ADDRESS_ID#2372,CA_STREET_NUMBER#2373,CA_STREET_NAME#2374,CA_STREET_TYPE#2375,CA_SUITE_NUMBER#2376,CA_CITY#2377,CA_COUNTY#2378,CA_STATE#2379,CA_ZIP#2380,CA_COUNTRY#2381,CA_GMT_OFFSET#2382,CA_LOCATION_TYPE#2383,IGNITE_INDEX#2384L]
IgniteSQLRelation[table=HIVE_customer_address_2_1546577865912]
+- Project [c_customer_sk#2519, c_customer_id#2520, c_current_cdemo_sk#2521,
c_current_hdemo_sk#2522, c_current_addr_sk#2523, c_first_shipto_date_sk#2524,
c_first_sales_date_sk#2525, c_salutation#2526, c_first_name#2527,
c_last_name#2528, c_preferred_cust_flag#2529, c_birth_day#2530,
c_birth_month#2531, c_birth_year#2532, c_birth_country#2533, c_login#2534,
c_email_address#2535, c_last_review_date#2536|#2519, c_customer_id#2520,
c_current_cdemo_sk#2521, c_current_hdemo_sk#2522, c_current_addr_sk#2523,
c_first_shipto_date_sk#2524, c_first_sales_date_sk#2525, c_salutation#2526,
c_first_name#2527, c_last_name#2528, c_preferred_cust_flag#2529,
c_birth_day#2530, c_birth_month#2531, c_birth_year#2532, c_birth_country#2533,
c_login#2534, c_email_address#2535, c_last_review_date#2536]
+- Project [C_CUSTOMER_SK#2481 AS c_customer_sk#2519, C_CUSTOMER_ID#2482 AS
c_customer_id#2520, C_CURRENT_CDEMO_SK#2483 AS c_current_cdemo_sk#2521,
C_CURRENT_HDEMO_SK#2484 AS c_current_hdemo_sk#2522, C_CURRENT_ADDR_SK#2485 AS
c_current_addr_sk#2523, C_FIRST_SHIPTO_DATE_SK#2486 AS
c_first_shipto_date_sk#2524, C_FIRST_SALES_DATE_SK#2487 AS
c_first_sales_date_sk#2525, C_SALUTATION#2488 AS c_salutation#2526,
C_FIRST_NAME#2489 AS c_first_name#2527, C_LAST_NAME#2490 AS c_last_name#2528,
C_PREFERRED_CUST_FLAG#2491 AS c_preferred_cust_flag#2529, C_BIRTH_DAY#2492 AS
c_birth_day#2530, C_BIRTH_MONTH#2493 AS c_birth_month#2531, C_BIRTH_YEAR#2494
AS c_birth_year#2532, C_BIRTH_COUNTRY#2495 AS c_birth_country#2533,
C_LOGIN#2496 AS c_login#2534, C_EMAIL_ADDRESS#2497 AS c_email_address#2535,
C_LAST_REVIEW_DATE#2498 AS c_last_review_date#2536, IGNITE_INDEX#2499L AS
ignite_index#2537L|#2481 AS c_customer_sk#2519, C_CUSTOMER_ID#2482 AS
c_customer_id#2520, C_CURRENT_CDEMO_SK#2483 AS c_current_cdemo_sk#2521,
C_CURRENT_HDEMO_SK#2484 AS c_current_hdemo_sk#2522, C_CURRENT_ADDR_SK#2485 AS
c_current_addr_sk#2523, C_FIRST_SHIPTO_DATE_SK#2486 AS
c_first_shipto_date_sk#2524, C_FIRST_SALES_DATE_SK#2487 AS
c_first_sales_date_sk#2525, C_SALUTATION#2488 AS c_salutation#2526,
C_FIRST_NAME#2489 AS c_first_name#2527, C_LAST_NAME#2490 AS c_last_name#2528,
C_PREFERRED_CUST_FLAG#2491 AS c_preferred_cust_flag#2529, C_BIRTH_DAY#2492 AS
c_birth_day#2530, C_BIRTH_MONTH#2493 AS c_birth_month#2531, C_BIRTH_YEAR#2494
AS c_birth_year#2532, C_BIRTH_COUNTRY#2495 AS c_birth_country#2533,
C_LOGIN#2496 AS c_login#2534, C_EMAIL_ADDRESS#2497 AS c_email_address#2535,
C_LAST_REVIEW_DATE#2498 AS c_last_review_date#2536, IGNITE_INDEX#2499L AS
ignite_index#2537L]
+-
Relation[C_CUSTOMER_SK#2481,C_CUSTOMER_ID#2482,C_CURRENT_CDEMO_SK#2483,C_CURRENT_HDEMO_SK#2484,C_CURRENT_ADDR_SK#2485,C_FIRST_SHIPTO_DATE_SK#2486,C_FIRST_SALES_DATE_SK#2487,C_SALUTATION#2488,C_FIRST_NAME#2489,C_LAST_NAME#2490,C_PREFERRED_CUST_FLAG#2491,C_BIRTH_DAY#2492,C_BIRTH_MONTH#2493,C_BIRTH_YEAR#2494,C_BIRTH_COUNTRY#2495,C_LOGIN#2496,C_EMAIL_ADDRESS#2497,C_LAST_REVIEW_DATE#2498,IGNITE_INDEX#2499L|#2481,C_CUSTOMER_ID#2482,C_CURRENT_CDEMO_SK#2483,C_CURRENT_HDEMO_SK#2484,C_CURRENT_ADDR_SK#2485,C_FIRST_SHIPTO_DATE_SK#2486,C_FIRST_SALES_DATE_SK#2487,C_SALUTATION#2488,C_FIRST_NAME#2489,C_LAST_NAME#2490,C_PREFERRED_CUST_FLAG#2491,C_BIRTH_DAY#2492,C_BIRTH_MONTH#2493,C_BIRTH_YEAR#2494,C_BIRTH_COUNTRY#2495,C_LOGIN#2496,C_EMAIL_ADDRESS#2497,C_LAST_REVIEW_DATE#2498,IGNITE_INDEX#2499L]
IgniteSQLRelation[table=POSTGRES_customer_1_1546598025406]
== Optimized Logical Plan ==
Relation[CA_ADDRESS_SK#2399,CA_ADDRESS_ID#2400,CA_STREET_NUMBER#2401,CA_STREET_NAME#2402,CA_STREET_TYPE#2403,CA_SUITE_NUMBER#2404,CA_CITY#2405,CA_COUNTY#2406,CA_STATE#2407,CA_ZIP#2408,CA_COUNTRY#2409,CA_GMT_OFFSET#2410,CA_LOCATION_TYPE#2411,C_CUSTOMER_SK#2519,C_CUSTOMER_ID#2520,C_CURRENT_CDEMO_SK#2521,C_CURRENT_HDEMO_SK#2522,C_CURRENT_ADDR_SK#2523,C_FIRST_SHIPTO_DATE_SK#2524,C_FIRST_SALES_DATE_SK#2525,C_SALUTATION#2526,C_FIRST_NAME#2527,C_LAST_NAME#2528,C_PREFERRED_CUST_FLAG#2529,...
7 more
fields|#2399,CA_ADDRESS_ID#2400,CA_STREET_NUMBER#2401,CA_STREET_NAME#2402,CA_STREET_TYPE#2403,CA_SUITE_NUMBER#2404,CA_CITY#2405,CA_COUNTY#2406,CA_STATE#2407,CA_ZIP#2408,CA_COUNTRY#2409,CA_GMT_OFFSET#2410,CA_LOCATION_TYPE#2411,C_CUSTOMER_SK#2519,C_CUSTOMER_ID#2520,C_CURRENT_CDEMO_SK#2521,C_CURRENT_HDEMO_SK#2522,C_CURRENT_ADDR_SK#2523,C_FIRST_SHIPTO_DATE_SK#2524,C_FIRST_SALES_DATE_SK#2525,C_SALUTATION#2526,C_FIRST_NAME#2527,C_LAST_NAME#2528,C_PREFERRED_CUST_FLAG#2529,...
7 more fields] IgniteSQLAccumulatorRelation(columns=[CA_ADDRESS_SK,
CA_ADDRESS_ID, CA_STREET_NUMBER, CA_STREET_NAME, CA_STREET_TYPE,
CA_SUITE_NUMBER, CA_CITY, CA_COUNTY, CA_STATE, CA_ZIP, CA_COUNTRY,
CA_GMT_OFFSET, CA_LOCATION_TYPE, C_CUSTOMER_SK, C_CUSTOMER_ID,
C_CURRENT_CDEMO_SK, C_CURRENT_HDEMO_SK, C_CURRENT_ADDR_SK,
C_FIRST_SHIPTO_DATE_SK, C_FIRST_SALES_DATE_SK, C_SALUTATION, C_FIRST_NAME,
C_LAST_NAME, C_PREFERRED_CUST_FLAG, C_BIRTH_DAY, C_BIRTH_MONTH, C_BIRTH_YEAR,
C_BIRTH_COUNTRY, C_LOGIN, C_EMAIL_ADDRESS, C_LAST_REVIEW_DATE], qry=SELECT
HIVE_customer_address_2_1546577865912.CA_ADDRESS_SK,
HIVE_customer_address_2_1546577865912.CA_ADDRESS_ID,
HIVE_customer_address_2_1546577865912.CA_STREET_NUMBER,
HIVE_customer_address_2_1546577865912.CA_STREET_NAME,
HIVE_customer_address_2_1546577865912.CA_STREET_TYPE,
HIVE_customer_address_2_1546577865912.CA_SUITE_NUMBER,
HIVE_customer_address_2_1546577865912.CA_CITY,
HIVE_customer_address_2_1546577865912.CA_COUNTY,
HIVE_customer_address_2_1546577865912.CA_STATE,
HIVE_customer_address_2_1546577865912.CA_ZIP,
HIVE_customer_address_2_1546577865912.CA_COUNTRY,
HIVE_customer_address_2_1546577865912.CA_GMT_OFFSET,
HIVE_customer_address_2_1546577865912.CA_LOCATION_TYPE,
POSTGRES_customer_1_1546598025406.C_CUSTOMER_SK,
POSTGRES_customer_1_1546598025406.C_CUSTOMER_ID,
POSTGRES_customer_1_1546598025406.C_CURRENT_CDEMO_SK,
POSTGRES_customer_1_1546598025406.C_CURRENT_HDEMO_SK,
POSTGRES_customer_1_1546598025406.C_CURRENT_ADDR_SK,
POSTGRES_customer_1_1546598025406.C_FIRST_SHIPTO_DATE_SK,
POSTGRES_customer_1_1546598025406.C_FIRST_SALES_DATE_SK,
POSTGRES_customer_1_1546598025406.C_SALUTATION,
POSTGRES_customer_1_1546598025406.C_FIRST_NAME,
POSTGRES_customer_1_1546598025406.C_LAST_NAME,
POSTGRES_customer_1_1546598025406.C_PREFERRED_CUST_FLAG,
POSTGRES_customer_1_1546598025406.C_BIRTH_DAY,
POSTGRES_customer_1_1546598025406.C_BIRTH_MONTH,
POSTGRES_customer_1_1546598025406.C_BIRTH_YEAR,
POSTGRES_customer_1_1546598025406.C_BIRTH_COUNTRY,
POSTGRES_customer_1_1546598025406.C_LOGIN,
POSTGRES_customer_1_1546598025406.C_EMAIL_ADDRESS,
POSTGRES_customer_1_1546598025406.C_LAST_REVIEW_DATE FROM
HIVE_customer_address_2_1546577865912 JOIN POSTGRES_customer_1_1546598025406 ON
HIVE_customer_address_2_1546577865912.ca_address_sk =
POSTGRES_customer_1_1546598025406.c_customer_sk WHERE
POSTGRES_customer_1_1546598025406.CA_ADDRESS_SK IS NOT NULL AND
POSTGRES_customer_1_1546598025406.C_CUSTOMER_SK IS NOT NULL)
== Physical Plan ==
*(1) Scan IgniteSQLAccumulatorRelation(columns=[CA_ADDRESS_SK, CA_ADDRESS_ID,
CA_STREET_NUMBER, CA_STREET_NAME, CA_STREET_TYPE, CA_SUITE_NUMBER, CA_CITY,
CA_COUNTY, CA_STATE, CA_ZIP, CA_COUNTRY, CA_GMT_OFFSET, CA_LOCATION_TYPE,
C_CUSTOMER_SK, C_CUSTOMER_ID, C_CURRENT_CDEMO_SK, C_CURRENT_HDEMO_SK,
C_CURRENT_ADDR_SK, C_FIRST_SHIPTO_DATE_SK, C_FIRST_SALES_DATE_SK, C_SALUTATION,
C_FIRST_NAME, C_LAST_NAME, C_PREFERRED_CUST_FLAG, C_BIRTH_DAY, C_BIRTH_MONTH,
C_BIRTH_YEAR, C_BIRTH_COUNTRY, C_LOGIN, C_EMAIL_ADDRESS, C_LAST_REVIEW_DATE],
qry=SELECT HIVE_customer_address_2_1546577865912.CA_ADDRESS_SK,
HIVE_customer_address_2_1546577865912.CA_ADDRESS_ID,
HIVE_customer_address_2_1546577865912.CA_STREET_NUMBER,
HIVE_customer_address_2_1546577865912.CA_STREET_NAME,
HIVE_customer_address_2_1546577865912.CA_STREET_TYPE,
HIVE_customer_address_2_1546577865912.CA_SUITE_NUMBER,
HIVE_customer_address_2_1546577865912.CA_CITY,
HIVE_customer_address_2_1546577865912.CA_COUNTY,
HIVE_customer_address_2_1546577865912.CA_STATE,
HIVE_customer_address_2_1546577865912.CA_ZIP,
HIVE_customer_address_2_1546577865912.CA_COUNTRY,
HIVE_customer_address_2_1546577865912.CA_GMT_OFFSET,
HIVE_customer_address_2_1546577865912.CA_LOCATION_TYPE,
POSTGRES_customer_1_1546598025406.C_CUSTOMER_SK,
POSTGRES_customer_1_1546598025406.C_CUSTOMER_ID,
POSTGRES_customer_1_1546598025406.C_CURRENT_CDEMO_SK,
POSTGRES_customer_1_1546598025406.C_CURRENT_HDEMO_SK,
POSTGRES_customer_1_1546598025406.C_CURRENT_ADDR_SK,
POSTGRES_customer_1_1546598025406.C_FIRST_SHIPTO_DATE_SK,
POSTGRES_customer_1_1546598025406.C_FIRST_SALES_DATE_SK,
POSTGRES_customer_1_1546598025406.C_SALUTATION,
POSTGRES_customer_1_1546598025406.C_FIRST_NAME,
POSTGRES_customer_1_1546598025406.C_LAST_NAME,
POSTGRES_customer_1_1546598025406.C_PREFERRED_CUST_FLAG,
POSTGRES_customer_1_1546598025406.C_BIRTH_DAY,
POSTGRES_customer_1_1546598025406.C_BIRTH_MONTH,
POSTGRES_customer_1_1546598025406.C_BIRTH_YEAR,
POSTGRES_customer_1_1546598025406.C_BIRTH_COUNTRY,
POSTGRES_customer_1_1546598025406.C_LOGIN,
POSTGRES_customer_1_1546598025406.C_EMAIL_ADDRESS,
POSTGRES_customer_1_1546598025406.C_LAST_REVIEW_DATE FROM
HIVE_customer_address_2_1546577865912 JOIN POSTGRES_customer_1_1546598025406 ON
HIVE_customer_address_2_1546577865912.ca_address_sk =
POSTGRES_customer_1_1546598025406.c_customer_sk WHERE
POSTGRES_customer_1_1546598025406.CA_ADDRESS_SK IS NOT NULL AND
POSTGRES_customer_1_1546598025406.C_CUSTOMER_SK IS NOT NULL)
[CA_ADDRESS_SK#2399,CA_ADDRESS_ID#2400,CA_STREET_NUMBER#2401,CA_STREET_NAME#2402,CA_STREET_TYPE#2403,CA_SUITE_NUMBER#2404,CA_CITY#2405,CA_COUNTY#2406,CA_STATE#2407,CA_ZIP#2408,CA_COUNTRY#2409,CA_GMT_OFFSET#2410,CA_LOCATION_TYPE#2411,C_CUSTOMER_SK#2519,C_CUSTOMER_ID#2520,C_CURRENT_CDEMO_SK#2521,C_CURRENT_HDEMO_SK#2522,C_CURRENT_ADDR_SK#2523,C_FIRST_SHIPTO_DATE_SK#2524,C_FIRST_SALES_DATE_SK#2525,C_SALUTATION#2526,C_FIRST_NAME#2527,C_LAST_NAME#2528,C_PREFERRED_CUST_FLAG#2529,...
7 more
fields|#2399,CA_ADDRESS_ID#2400,CA_STREET_NUMBER#2401,CA_STREET_NAME#2402,CA_STREET_TYPE#2403,CA_SUITE_NUMBER#2404,CA_CITY#2405,CA_COUNTY#2406,CA_STATE#2407,CA_ZIP#2408,CA_COUNTRY#2409,CA_GMT_OFFSET#2410,CA_LOCATION_TYPE#2411,C_CUSTOMER_SK#2519,C_CUSTOMER_ID#2520,C_CURRENT_CDEMO_SK#2521,C_CURRENT_HDEMO_SK#2522,C_CURRENT_ADDR_SK#2523,C_FIRST_SHIPTO_DATE_SK#2524,C_FIRST_SALES_DATE_SK#2525,C_SALUTATION#2526,C_FIRST_NAME#2527,C_LAST_NAME#2528,C_PREFERRED_CUST_FLAG#2529,...
7 more fields] PushedFilters: [], ReadSchema:
struct<CA_ADDRESS_SK:int,CA_ADDRESS_ID:string,CA_STREET_NUMBER:string,CA_STREET_NAME:string,CA_ST...
was:
When we are loading two data-frames from ignite in spark and joining those to
dataframes, it is giving exception. We have checked the generated logical plan
and seems like it is wrong.
I am adding the stack trace and code
scala> val df1 = spark.read.format(FORMAT_IGNITE).option(OPTION_CONFIG_FILE,
CONFIG).option(OPTION_TABLE,
"HIVE_customer_address_2_1546577865912").load().toDF(schema1.columns
map(_.toLowerCase): _*)
df1: org.apache.spark.sql.DataFrame = [ca_address_sk: int, ca_address_id:
string ... 11 more fields]
scala> df1.show(1)
+---------------+--------------+++------------------------------++-----------------------------++------------------------++--------------++--------------------------+----------------
|ca_address_sk|ca_address_id|ca_street_number|ca_street_name|ca_street_type|ca_suite_number|ca_city|ca_county|ca_state|ca_zip|ca_country|ca_gmt_offset|ca_location_type|
+---------------+--------------+++------------------------------++-----------------------------++------------------------++--------------++--------------------------+----------------
|1|AAAAAAAABAAAAAAA|18|Jackson|Parkway|Suite 280|Fairfield|Maricopa
County|AZ|86192|United States|-7.00|condo|
+---------------+--------------+++------------------------------++-----------------------------++------------------------++--------------++--------------------------+----------------
only showing top 1 row
scala> val df2 = spark.read.format(FORMAT_IGNITE).option(OPTION_CONFIG_FILE,
CONFIG).option(OPTION_TABLE,
"POSTGRES_customer_1_1546598025406").load().toDF(schema1.columns
map(_.toLowerCase): _*)
df2: org.apache.spark.sql.DataFrame] = [c_customer_sk: int, c_customer_id:
string ... 16 more fields]
scala> df2.show(1)
+---------------+--------------+++------------------------------------++---------------------------------------++---------------------------------++----------------------------------------++--------------------------------++-------------------------++----------------------++--------------------------------------
|c_customer_sk|c_customer_id|c_current_cdemo_sk|c_current_hdemo_sk|c_current_addr_sk|c_first_shipto_date_sk|c_first_sales_date_sk|c_salutation|c_first_name|c_last_name|c_preferred_cust_flag|c_birth_day|c_birth_month|c_birth_year|c_birth_country|c_login|c_email_address|c_last_review_date|
+---------------+--------------+++------------------------------------++---------------------------------------++---------------------------------++----------------------------------------++--------------------------------++-------------------------++----------------------++--------------------------------------
|7288|AAAAAAAAIHMBAAAA|1461725|4938|18198|2450838|2450808|Sir|Steven|Storey
...|Y|1|2|1967|QATAR|null|Steven.Storey@QdG...|2452528|
+---------------+--------------+++------------------------------------++---------------------------------------++---------------------------------++----------------------------------------++--------------------------------++-------------------------++----------------------++--------------------------------------
scala> df1.join(df2, df1.col("ca_address_sk") === df2.col("c_customer_sk"),
"inner")
res64: org.apache.spark.sql.DataFrame = [ca_address_sk: int, ca_address_id:
string ... 29 more fields]
scala> res64.show
19/01/04 16:50:07 ERROR Executor: Exception in task 0.0 in stage 15.0 (TID 15)
javax.cache.CacheException: Failed to parse query. Column
"POSTGRES_CUSTOMER_1_1546598025406.CA_ADDRESS_SK" not found; SQL statement:
SELECT CAST(HIVE_customer_address_2_1546577865912.CA_ADDRESS_SK AS VARCHAR) AS
ca_address_sk, HIVE_customer_address_2_1546577865912.CA_ADDRESS_ID,
HIVE_customer_address_2_1546577865912.CA_STREET_NUMBER,
HIVE_customer_address_2_1546577865912.CA_STREET_NAME,
HIVE_customer_address_2_1546577865912.CA_STREET_TYPE,
HIVE_customer_address_2_1546577865912.CA_SUITE_NUMBER,
HIVE_customer_address_2_1546577865912.CA_CITY,
HIVE_customer_address_2_1546577865912.CA_COUNTY,
HIVE_customer_address_2_1546577865912.CA_STATE,
HIVE_customer_address_2_1546577865912.CA_ZIP,
HIVE_customer_address_2_1546577865912.CA_COUNTRY,
CAST(HIVE_customer_address_2_1546577865912.CA_GMT_OFFSET AS VARCHAR) AS
ca_gmt_offset, HIVE_customer_address_2_1546577865912.CA_LOCATION_TYPE,
CAST(POSTGRES_customer_1_1546598025406.C_CUSTOMER_SK AS VARCHAR) AS
c_customer_sk, POSTGRES_customer_1_1546598025406.C_CUSTOMER_ID,
CAST(POSTGRES_customer_1_1546598025406.C_CURRENT_CDEMO_SK AS VARCHAR) AS
c_current_cdemo_sk, CAST(POSTGRES_customer_1_1546598025406.C_CURRENT_HDEMO_SK
AS VARCHAR) AS c_current_hdemo_sk,
CAST(POSTGRES_customer_1_1546598025406.C_CURRENT_ADDR_SK AS VARCHAR) AS
c_current_addr_sk,
CAST(POSTGRES_customer_1_1546598025406.C_FIRST_SHIPTO_DATE_SK AS VARCHAR) AS
c_first_shipto_date_sk,
CAST(POSTGRES_customer_1_1546598025406.C_FIRST_SALES_DATE_SK AS VARCHAR) AS
c_first_sales_date_sk, POSTGRES_customer_1_1546598025406.C_SALUTATION,
POSTGRES_customer_1_1546598025406.C_FIRST_NAME,
POSTGRES_customer_1_1546598025406.C_LAST_NAME,
POSTGRES_customer_1_1546598025406.C_PREFERRED_CUST_FLAG,
CAST(POSTGRES_customer_1_1546598025406.C_BIRTH_DAY AS VARCHAR) AS c_birth_day,
CAST(POSTGRES_customer_1_1546598025406.C_BIRTH_MONTH AS VARCHAR) AS
c_birth_month, CAST(POSTGRES_customer_1_1546598025406.C_BIRTH_YEAR AS VARCHAR)
AS c_birth_year, POSTGRES_customer_1_1546598025406.C_BIRTH_COUNTRY,
POSTGRES_customer_1_1546598025406.C_LOGIN,
POSTGRES_customer_1_1546598025406.C_EMAIL_ADDRESS,
POSTGRES_customer_1_1546598025406.C_LAST_REVIEW_DATE FROM
HIVE_customer_address_2_1546577865912 JOIN POSTGRES_customer_1_1546598025406 ON
HIVE_customer_address_2_1546577865912.ca_address_sk =
POSTGRES_customer_1_1546598025406.c_customer_sk WHERE
POSTGRES_customer_1_1546598025406.CA_ADDRESS_SK IS NOT NULL AND
POSTGRES_customer_1_1546598025406.C_CUSTOMER_SK IS NOT NULL LIMIT 21 [42122-195]
at
org.apache.ignite.internal.processors.cache.IgniteCacheProxyImpl.query(IgniteCacheProxyImpl.java:676)
at
org.apache.ignite.internal.processors.cache.GatewayProtectedCacheProxy.query(GatewayProtectedCacheProxy.java:344)
at org.apache.ignite.spark.impl.IgniteSqlRDD.compute(IgniteSqlRDD.scala:37)
at
org.apache.ignite.spark.impl.IgniteSQLDataFrameRDD.compute(IgniteSQLDataFrameRDD.scala:74)
at org.apache.spark.rdd.RDD.computeOrReadCheckpoint(RDD.scala:324)
at org.apache.spark.rdd.RDD.iterator(RDD.scala:288)
at org.apache.spark.rdd.MapPartitionsRDD.compute(MapPartitionsRDD.scala:38)
at org.apache.spark.rdd.RDD.computeOrReadCheckpoint(RDD.scala:324)
at org.apache.spark.rdd.RDD.iterator(RDD.scala:288)
at org.apache.spark.rdd.MapPartitionsRDD.compute(MapPartitionsRDD.scala:38)
at org.apache.spark.rdd.RDD.computeOrReadCheckpoint(RDD.scala:324)
at org.apache.spark.rdd.RDD.iterator(RDD.scala:288)
at org.apache.spark.rdd.MapPartitionsRDD.compute(MapPartitionsRDD.scala:38)
at org.apache.spark.rdd.RDD.computeOrReadCheckpoint(RDD.scala:324)
at org.apache.spark.rdd.RDD.iterator(RDD.scala:288)
at org.apache.spark.scheduler.ResultTask.runTask(ResultTask.scala:87)
at org.apache.spark.scheduler.Task.run(Task.scala:109)
at org.apache.spark.executor.Executor$TaskRunner.run(Executor.scala:345)
at
java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
at
java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
at java.lang.Thread.run(Thread.java:745)
//Generated Plan
scala> res67.explain(true)
== Parsed Logical Plan ==
Join Inner, (ca_address_sk#2399 = c_customer_sk#2519)
:- Project [ca_address_sk#2399, ca_address_id#2400, ca_street_number#2401,
ca_street_name#2402, ca_street_type#2403, ca_suite_number#2404, ca_city#2405,
ca_county#2406, ca_state#2407, ca_zip#2408, ca_country#2409,
ca_gmt_offset#2410, ca_location_type#2411|#2399, ca_address_id#2400,
ca_street_number#2401, ca_street_name#2402, ca_street_type#2403,
ca_suite_number#2404, ca_city#2405, ca_county#2406, ca_state#2407, ca_zip#2408,
ca_country#2409, ca_gmt_offset#2410, ca_location_type#2411]
: +- Project [CA_ADDRESS_SK#2371 AS ca_address_sk#2399, CA_ADDRESS_ID#2372 AS
ca_address_id#2400, CA_STREET_NUMBER#2373 AS ca_street_number#2401,
CA_STREET_NAME#2374 AS ca_street_name#2402, CA_STREET_TYPE#2375 AS
ca_street_type#2403, CA_SUITE_NUMBER#2376 AS ca_suite_number#2404, CA_CITY#2377
AS ca_city#2405, CA_COUNTY#2378 AS ca_county#2406, CA_STATE#2379 AS
ca_state#2407, CA_ZIP#2380 AS ca_zip#2408, CA_COUNTRY#2381 AS ca_country#2409,
CA_GMT_OFFSET#2382 AS ca_gmt_offset#2410, CA_LOCATION_TYPE#2383 AS
ca_location_type#2411, IGNITE_INDEX#2384L AS ignite_index#2412L|#2371 AS
ca_address_sk#2399, CA_ADDRESS_ID#2372 AS ca_address_id#2400,
CA_STREET_NUMBER#2373 AS ca_street_number#2401, CA_STREET_NAME#2374 AS
ca_street_name#2402, CA_STREET_TYPE#2375 AS ca_street_type#2403,
CA_SUITE_NUMBER#2376 AS ca_suite_number#2404, CA_CITY#2377 AS ca_city#2405,
CA_COUNTY#2378 AS ca_county#2406, CA_STATE#2379 AS ca_state#2407, CA_ZIP#2380
AS ca_zip#2408, CA_COUNTRY#2381 AS ca_country#2409, CA_GMT_OFFSET#2382 AS
ca_gmt_offset#2410, CA_LOCATION_TYPE#2383 AS ca_location_type#2411,
IGNITE_INDEX#2384L AS ignite_index#2412L]
: +-
Relation[CA_ADDRESS_SK#2371,CA_ADDRESS_ID#2372,CA_STREET_NUMBER#2373,CA_STREET_NAME#2374,CA_STREET_TYPE#2375,CA_SUITE_NUMBER#2376,CA_CITY#2377,CA_COUNTY#2378,CA_STATE#2379,CA_ZIP#2380,CA_COUNTRY#2381,CA_GMT_OFFSET#2382,CA_LOCATION_TYPE#2383,IGNITE_INDEX#2384L|#2371,CA_ADDRESS_ID#2372,CA_STREET_NUMBER#2373,CA_STREET_NAME#2374,CA_STREET_TYPE#2375,CA_SUITE_NUMBER#2376,CA_CITY#2377,CA_COUNTY#2378,CA_STATE#2379,CA_ZIP#2380,CA_COUNTRY#2381,CA_GMT_OFFSET#2382,CA_LOCATION_TYPE#2383,IGNITE_INDEX#2384L]
IgniteSQLRelation[table=HIVE_customer_address_2_1546577865912]
+- Project [c_customer_sk#2519, c_customer_id#2520, c_current_cdemo_sk#2521,
c_current_hdemo_sk#2522, c_current_addr_sk#2523, c_first_shipto_date_sk#2524,
c_first_sales_date_sk#2525, c_salutation#2526, c_first_name#2527,
c_last_name#2528, c_preferred_cust_flag#2529, c_birth_day#2530,
c_birth_month#2531, c_birth_year#2532, c_birth_country#2533, c_login#2534,
c_email_address#2535, c_last_review_date#2536|#2519, c_customer_id#2520,
c_current_cdemo_sk#2521, c_current_hdemo_sk#2522, c_current_addr_sk#2523,
c_first_shipto_date_sk#2524, c_first_sales_date_sk#2525, c_salutation#2526,
c_first_name#2527, c_last_name#2528, c_preferred_cust_flag#2529,
c_birth_day#2530, c_birth_month#2531, c_birth_year#2532, c_birth_country#2533,
c_login#2534, c_email_address#2535, c_last_review_date#2536]
+- Project [C_CUSTOMER_SK#2481 AS c_customer_sk#2519, C_CUSTOMER_ID#2482 AS
c_customer_id#2520, C_CURRENT_CDEMO_SK#2483 AS c_current_cdemo_sk#2521,
C_CURRENT_HDEMO_SK#2484 AS c_current_hdemo_sk#2522, C_CURRENT_ADDR_SK#2485 AS
c_current_addr_sk#2523, C_FIRST_SHIPTO_DATE_SK#2486 AS
c_first_shipto_date_sk#2524, C_FIRST_SALES_DATE_SK#2487 AS
c_first_sales_date_sk#2525, C_SALUTATION#2488 AS c_salutation#2526,
C_FIRST_NAME#2489 AS c_first_name#2527, C_LAST_NAME#2490 AS c_last_name#2528,
C_PREFERRED_CUST_FLAG#2491 AS c_preferred_cust_flag#2529, C_BIRTH_DAY#2492 AS
c_birth_day#2530, C_BIRTH_MONTH#2493 AS c_birth_month#2531, C_BIRTH_YEAR#2494
AS c_birth_year#2532, C_BIRTH_COUNTRY#2495 AS c_birth_country#2533,
C_LOGIN#2496 AS c_login#2534, C_EMAIL_ADDRESS#2497 AS c_email_address#2535,
C_LAST_REVIEW_DATE#2498 AS c_last_review_date#2536, IGNITE_INDEX#2499L AS
ignite_index#2537L|#2481 AS c_customer_sk#2519, C_CUSTOMER_ID#2482 AS
c_customer_id#2520, C_CURRENT_CDEMO_SK#2483 AS c_current_cdemo_sk#2521,
C_CURRENT_HDEMO_SK#2484 AS c_current_hdemo_sk#2522, C_CURRENT_ADDR_SK#2485 AS
c_current_addr_sk#2523, C_FIRST_SHIPTO_DATE_SK#2486 AS
c_first_shipto_date_sk#2524, C_FIRST_SALES_DATE_SK#2487 AS
c_first_sales_date_sk#2525, C_SALUTATION#2488 AS c_salutation#2526,
C_FIRST_NAME#2489 AS c_first_name#2527, C_LAST_NAME#2490 AS c_last_name#2528,
C_PREFERRED_CUST_FLAG#2491 AS c_preferred_cust_flag#2529, C_BIRTH_DAY#2492 AS
c_birth_day#2530, C_BIRTH_MONTH#2493 AS c_birth_month#2531, C_BIRTH_YEAR#2494
AS c_birth_year#2532, C_BIRTH_COUNTRY#2495 AS c_birth_country#2533,
C_LOGIN#2496 AS c_login#2534, C_EMAIL_ADDRESS#2497 AS c_email_address#2535,
C_LAST_REVIEW_DATE#2498 AS c_last_review_date#2536, IGNITE_INDEX#2499L AS
ignite_index#2537L]
+-
Relation[C_CUSTOMER_SK#2481,C_CUSTOMER_ID#2482,C_CURRENT_CDEMO_SK#2483,C_CURRENT_HDEMO_SK#2484,C_CURRENT_ADDR_SK#2485,C_FIRST_SHIPTO_DATE_SK#2486,C_FIRST_SALES_DATE_SK#2487,C_SALUTATION#2488,C_FIRST_NAME#2489,C_LAST_NAME#2490,C_PREFERRED_CUST_FLAG#2491,C_BIRTH_DAY#2492,C_BIRTH_MONTH#2493,C_BIRTH_YEAR#2494,C_BIRTH_COUNTRY#2495,C_LOGIN#2496,C_EMAIL_ADDRESS#2497,C_LAST_REVIEW_DATE#2498,IGNITE_INDEX#2499L|#2481,C_CUSTOMER_ID#2482,C_CURRENT_CDEMO_SK#2483,C_CURRENT_HDEMO_SK#2484,C_CURRENT_ADDR_SK#2485,C_FIRST_SHIPTO_DATE_SK#2486,C_FIRST_SALES_DATE_SK#2487,C_SALUTATION#2488,C_FIRST_NAME#2489,C_LAST_NAME#2490,C_PREFERRED_CUST_FLAG#2491,C_BIRTH_DAY#2492,C_BIRTH_MONTH#2493,C_BIRTH_YEAR#2494,C_BIRTH_COUNTRY#2495,C_LOGIN#2496,C_EMAIL_ADDRESS#2497,C_LAST_REVIEW_DATE#2498,IGNITE_INDEX#2499L]
IgniteSQLRelation[table=POSTGRES_customer_1_1546598025406]
== Analyzed Logical Plan ==
ca_address_sk: int, ca_address_id: string, ca_street_number: string,
ca_street_name: string, ca_street_type: string, ca_suite_number: string,
ca_city: string, ca_county: string, ca_state: string, ca_zip: string,
ca_country: string, ca_gmt_offset: decimal(38,3), ca_location_type: string,
c_customer_sk: int, c_customer_id: string, c_current_cdemo_sk: int,
c_current_hdemo_sk: int, c_current_addr_sk: int, c_first_shipto_date_sk: int,
c_first_sales_date_sk: int, c_salutation: string, c_first_name: string,
c_last_name: string, c_preferred_cust_flag: string, ... 7 more fields
Join Inner, (ca_address_sk#2399 = c_customer_sk#2519)
:- Project [ca_address_sk#2399, ca_address_id#2400, ca_street_number#2401,
ca_street_name#2402, ca_street_type#2403, ca_suite_number#2404, ca_city#2405,
ca_county#2406, ca_state#2407, ca_zip#2408, ca_country#2409,
ca_gmt_offset#2410, ca_location_type#2411|#2399, ca_address_id#2400,
ca_street_number#2401, ca_street_name#2402, ca_street_type#2403,
ca_suite_number#2404, ca_city#2405, ca_county#2406, ca_state#2407, ca_zip#2408,
ca_country#2409, ca_gmt_offset#2410, ca_location_type#2411]
: +- Project [CA_ADDRESS_SK#2371 AS ca_address_sk#2399, CA_ADDRESS_ID#2372 AS
ca_address_id#2400, CA_STREET_NUMBER#2373 AS ca_street_number#2401,
CA_STREET_NAME#2374 AS ca_street_name#2402, CA_STREET_TYPE#2375 AS
ca_street_type#2403, CA_SUITE_NUMBER#2376 AS ca_suite_number#2404, CA_CITY#2377
AS ca_city#2405, CA_COUNTY#2378 AS ca_county#2406, CA_STATE#2379 AS
ca_state#2407, CA_ZIP#2380 AS ca_zip#2408, CA_COUNTRY#2381 AS ca_country#2409,
CA_GMT_OFFSET#2382 AS ca_gmt_offset#2410, CA_LOCATION_TYPE#2383 AS
ca_location_type#2411, IGNITE_INDEX#2384L AS ignite_index#2412L|#2371 AS
ca_address_sk#2399, CA_ADDRESS_ID#2372 AS ca_address_id#2400,
CA_STREET_NUMBER#2373 AS ca_street_number#2401, CA_STREET_NAME#2374 AS
ca_street_name#2402, CA_STREET_TYPE#2375 AS ca_street_type#2403,
CA_SUITE_NUMBER#2376 AS ca_suite_number#2404, CA_CITY#2377 AS ca_city#2405,
CA_COUNTY#2378 AS ca_county#2406, CA_STATE#2379 AS ca_state#2407, CA_ZIP#2380
AS ca_zip#2408, CA_COUNTRY#2381 AS ca_country#2409, CA_GMT_OFFSET#2382 AS
ca_gmt_offset#2410, CA_LOCATION_TYPE#2383 AS ca_location_type#2411,
IGNITE_INDEX#2384L AS ignite_index#2412L]
: +-
Relation[CA_ADDRESS_SK#2371,CA_ADDRESS_ID#2372,CA_STREET_NUMBER#2373,CA_STREET_NAME#2374,CA_STREET_TYPE#2375,CA_SUITE_NUMBER#2376,CA_CITY#2377,CA_COUNTY#2378,CA_STATE#2379,CA_ZIP#2380,CA_COUNTRY#2381,CA_GMT_OFFSET#2382,CA_LOCATION_TYPE#2383,IGNITE_INDEX#2384L|#2371,CA_ADDRESS_ID#2372,CA_STREET_NUMBER#2373,CA_STREET_NAME#2374,CA_STREET_TYPE#2375,CA_SUITE_NUMBER#2376,CA_CITY#2377,CA_COUNTY#2378,CA_STATE#2379,CA_ZIP#2380,CA_COUNTRY#2381,CA_GMT_OFFSET#2382,CA_LOCATION_TYPE#2383,IGNITE_INDEX#2384L]
IgniteSQLRelation[table=HIVE_customer_address_2_1546577865912]
+- Project [c_customer_sk#2519, c_customer_id#2520, c_current_cdemo_sk#2521,
c_current_hdemo_sk#2522, c_current_addr_sk#2523, c_first_shipto_date_sk#2524,
c_first_sales_date_sk#2525, c_salutation#2526, c_first_name#2527,
c_last_name#2528, c_preferred_cust_flag#2529, c_birth_day#2530,
c_birth_month#2531, c_birth_year#2532, c_birth_country#2533, c_login#2534,
c_email_address#2535, c_last_review_date#2536|#2519, c_customer_id#2520,
c_current_cdemo_sk#2521, c_current_hdemo_sk#2522, c_current_addr_sk#2523,
c_first_shipto_date_sk#2524, c_first_sales_date_sk#2525, c_salutation#2526,
c_first_name#2527, c_last_name#2528, c_preferred_cust_flag#2529,
c_birth_day#2530, c_birth_month#2531, c_birth_year#2532, c_birth_country#2533,
c_login#2534, c_email_address#2535, c_last_review_date#2536]
+- Project [C_CUSTOMER_SK#2481 AS c_customer_sk#2519, C_CUSTOMER_ID#2482 AS
c_customer_id#2520, C_CURRENT_CDEMO_SK#2483 AS c_current_cdemo_sk#2521,
C_CURRENT_HDEMO_SK#2484 AS c_current_hdemo_sk#2522, C_CURRENT_ADDR_SK#2485 AS
c_current_addr_sk#2523, C_FIRST_SHIPTO_DATE_SK#2486 AS
c_first_shipto_date_sk#2524, C_FIRST_SALES_DATE_SK#2487 AS
c_first_sales_date_sk#2525, C_SALUTATION#2488 AS c_salutation#2526,
C_FIRST_NAME#2489 AS c_first_name#2527, C_LAST_NAME#2490 AS c_last_name#2528,
C_PREFERRED_CUST_FLAG#2491 AS c_preferred_cust_flag#2529, C_BIRTH_DAY#2492 AS
c_birth_day#2530, C_BIRTH_MONTH#2493 AS c_birth_month#2531, C_BIRTH_YEAR#2494
AS c_birth_year#2532, C_BIRTH_COUNTRY#2495 AS c_birth_country#2533,
C_LOGIN#2496 AS c_login#2534, C_EMAIL_ADDRESS#2497 AS c_email_address#2535,
C_LAST_REVIEW_DATE#2498 AS c_last_review_date#2536, IGNITE_INDEX#2499L AS
ignite_index#2537L|#2481 AS c_customer_sk#2519, C_CUSTOMER_ID#2482 AS
c_customer_id#2520, C_CURRENT_CDEMO_SK#2483 AS c_current_cdemo_sk#2521,
C_CURRENT_HDEMO_SK#2484 AS c_current_hdemo_sk#2522, C_CURRENT_ADDR_SK#2485 AS
c_current_addr_sk#2523, C_FIRST_SHIPTO_DATE_SK#2486 AS
c_first_shipto_date_sk#2524, C_FIRST_SALES_DATE_SK#2487 AS
c_first_sales_date_sk#2525, C_SALUTATION#2488 AS c_salutation#2526,
C_FIRST_NAME#2489 AS c_first_name#2527, C_LAST_NAME#2490 AS c_last_name#2528,
C_PREFERRED_CUST_FLAG#2491 AS c_preferred_cust_flag#2529, C_BIRTH_DAY#2492 AS
c_birth_day#2530, C_BIRTH_MONTH#2493 AS c_birth_month#2531, C_BIRTH_YEAR#2494
AS c_birth_year#2532, C_BIRTH_COUNTRY#2495 AS c_birth_country#2533,
C_LOGIN#2496 AS c_login#2534, C_EMAIL_ADDRESS#2497 AS c_email_address#2535,
C_LAST_REVIEW_DATE#2498 AS c_last_review_date#2536, IGNITE_INDEX#2499L AS
ignite_index#2537L]
+-
Relation[C_CUSTOMER_SK#2481,C_CUSTOMER_ID#2482,C_CURRENT_CDEMO_SK#2483,C_CURRENT_HDEMO_SK#2484,C_CURRENT_ADDR_SK#2485,C_FIRST_SHIPTO_DATE_SK#2486,C_FIRST_SALES_DATE_SK#2487,C_SALUTATION#2488,C_FIRST_NAME#2489,C_LAST_NAME#2490,C_PREFERRED_CUST_FLAG#2491,C_BIRTH_DAY#2492,C_BIRTH_MONTH#2493,C_BIRTH_YEAR#2494,C_BIRTH_COUNTRY#2495,C_LOGIN#2496,C_EMAIL_ADDRESS#2497,C_LAST_REVIEW_DATE#2498,IGNITE_INDEX#2499L|#2481,C_CUSTOMER_ID#2482,C_CURRENT_CDEMO_SK#2483,C_CURRENT_HDEMO_SK#2484,C_CURRENT_ADDR_SK#2485,C_FIRST_SHIPTO_DATE_SK#2486,C_FIRST_SALES_DATE_SK#2487,C_SALUTATION#2488,C_FIRST_NAME#2489,C_LAST_NAME#2490,C_PREFERRED_CUST_FLAG#2491,C_BIRTH_DAY#2492,C_BIRTH_MONTH#2493,C_BIRTH_YEAR#2494,C_BIRTH_COUNTRY#2495,C_LOGIN#2496,C_EMAIL_ADDRESS#2497,C_LAST_REVIEW_DATE#2498,IGNITE_INDEX#2499L]
IgniteSQLRelation[table=POSTGRES_customer_1_1546598025406]
== Optimized Logical Plan ==
Relation[CA_ADDRESS_SK#2399,CA_ADDRESS_ID#2400,CA_STREET_NUMBER#2401,CA_STREET_NAME#2402,CA_STREET_TYPE#2403,CA_SUITE_NUMBER#2404,CA_CITY#2405,CA_COUNTY#2406,CA_STATE#2407,CA_ZIP#2408,CA_COUNTRY#2409,CA_GMT_OFFSET#2410,CA_LOCATION_TYPE#2411,C_CUSTOMER_SK#2519,C_CUSTOMER_ID#2520,C_CURRENT_CDEMO_SK#2521,C_CURRENT_HDEMO_SK#2522,C_CURRENT_ADDR_SK#2523,C_FIRST_SHIPTO_DATE_SK#2524,C_FIRST_SALES_DATE_SK#2525,C_SALUTATION#2526,C_FIRST_NAME#2527,C_LAST_NAME#2528,C_PREFERRED_CUST_FLAG#2529,...
7 more
fields|#2399,CA_ADDRESS_ID#2400,CA_STREET_NUMBER#2401,CA_STREET_NAME#2402,CA_STREET_TYPE#2403,CA_SUITE_NUMBER#2404,CA_CITY#2405,CA_COUNTY#2406,CA_STATE#2407,CA_ZIP#2408,CA_COUNTRY#2409,CA_GMT_OFFSET#2410,CA_LOCATION_TYPE#2411,C_CUSTOMER_SK#2519,C_CUSTOMER_ID#2520,C_CURRENT_CDEMO_SK#2521,C_CURRENT_HDEMO_SK#2522,C_CURRENT_ADDR_SK#2523,C_FIRST_SHIPTO_DATE_SK#2524,C_FIRST_SALES_DATE_SK#2525,C_SALUTATION#2526,C_FIRST_NAME#2527,C_LAST_NAME#2528,C_PREFERRED_CUST_FLAG#2529,...
7 more fields] IgniteSQLAccumulatorRelation(columns=[CA_ADDRESS_SK,
CA_ADDRESS_ID, CA_STREET_NUMBER, CA_STREET_NAME, CA_STREET_TYPE,
CA_SUITE_NUMBER, CA_CITY, CA_COUNTY, CA_STATE, CA_ZIP, CA_COUNTRY,
CA_GMT_OFFSET, CA_LOCATION_TYPE, C_CUSTOMER_SK, C_CUSTOMER_ID,
C_CURRENT_CDEMO_SK, C_CURRENT_HDEMO_SK, C_CURRENT_ADDR_SK,
C_FIRST_SHIPTO_DATE_SK, C_FIRST_SALES_DATE_SK, C_SALUTATION, C_FIRST_NAME,
C_LAST_NAME, C_PREFERRED_CUST_FLAG, C_BIRTH_DAY, C_BIRTH_MONTH, C_BIRTH_YEAR,
C_BIRTH_COUNTRY, C_LOGIN, C_EMAIL_ADDRESS, C_LAST_REVIEW_DATE], qry=SELECT
HIVE_customer_address_2_1546577865912.CA_ADDRESS_SK,
HIVE_customer_address_2_1546577865912.CA_ADDRESS_ID,
HIVE_customer_address_2_1546577865912.CA_STREET_NUMBER,
HIVE_customer_address_2_1546577865912.CA_STREET_NAME,
HIVE_customer_address_2_1546577865912.CA_STREET_TYPE,
HIVE_customer_address_2_1546577865912.CA_SUITE_NUMBER,
HIVE_customer_address_2_1546577865912.CA_CITY,
HIVE_customer_address_2_1546577865912.CA_COUNTY,
HIVE_customer_address_2_1546577865912.CA_STATE,
HIVE_customer_address_2_1546577865912.CA_ZIP,
HIVE_customer_address_2_1546577865912.CA_COUNTRY,
HIVE_customer_address_2_1546577865912.CA_GMT_OFFSET,
HIVE_customer_address_2_1546577865912.CA_LOCATION_TYPE,
POSTGRES_customer_1_1546598025406.C_CUSTOMER_SK,
POSTGRES_customer_1_1546598025406.C_CUSTOMER_ID,
POSTGRES_customer_1_1546598025406.C_CURRENT_CDEMO_SK,
POSTGRES_customer_1_1546598025406.C_CURRENT_HDEMO_SK,
POSTGRES_customer_1_1546598025406.C_CURRENT_ADDR_SK,
POSTGRES_customer_1_1546598025406.C_FIRST_SHIPTO_DATE_SK,
POSTGRES_customer_1_1546598025406.C_FIRST_SALES_DATE_SK,
POSTGRES_customer_1_1546598025406.C_SALUTATION,
POSTGRES_customer_1_1546598025406.C_FIRST_NAME,
POSTGRES_customer_1_1546598025406.C_LAST_NAME,
POSTGRES_customer_1_1546598025406.C_PREFERRED_CUST_FLAG,
POSTGRES_customer_1_1546598025406.C_BIRTH_DAY,
POSTGRES_customer_1_1546598025406.C_BIRTH_MONTH,
POSTGRES_customer_1_1546598025406.C_BIRTH_YEAR,
POSTGRES_customer_1_1546598025406.C_BIRTH_COUNTRY,
POSTGRES_customer_1_1546598025406.C_LOGIN,
POSTGRES_customer_1_1546598025406.C_EMAIL_ADDRESS,
POSTGRES_customer_1_1546598025406.C_LAST_REVIEW_DATE FROM
HIVE_customer_address_2_1546577865912 JOIN POSTGRES_customer_1_1546598025406 ON
HIVE_customer_address_2_1546577865912.ca_address_sk =
POSTGRES_customer_1_1546598025406.c_customer_sk WHERE
POSTGRES_customer_1_1546598025406.CA_ADDRESS_SK IS NOT NULL AND
POSTGRES_customer_1_1546598025406.C_CUSTOMER_SK IS NOT NULL)
== Physical Plan ==
*(1) Scan IgniteSQLAccumulatorRelation(columns=[CA_ADDRESS_SK, CA_ADDRESS_ID,
CA_STREET_NUMBER, CA_STREET_NAME, CA_STREET_TYPE, CA_SUITE_NUMBER, CA_CITY,
CA_COUNTY, CA_STATE, CA_ZIP, CA_COUNTRY, CA_GMT_OFFSET, CA_LOCATION_TYPE,
C_CUSTOMER_SK, C_CUSTOMER_ID, C_CURRENT_CDEMO_SK, C_CURRENT_HDEMO_SK,
C_CURRENT_ADDR_SK, C_FIRST_SHIPTO_DATE_SK, C_FIRST_SALES_DATE_SK, C_SALUTATION,
C_FIRST_NAME, C_LAST_NAME, C_PREFERRED_CUST_FLAG, C_BIRTH_DAY, C_BIRTH_MONTH,
C_BIRTH_YEAR, C_BIRTH_COUNTRY, C_LOGIN, C_EMAIL_ADDRESS, C_LAST_REVIEW_DATE],
qry=SELECT HIVE_customer_address_2_1546577865912.CA_ADDRESS_SK,
HIVE_customer_address_2_1546577865912.CA_ADDRESS_ID,
HIVE_customer_address_2_1546577865912.CA_STREET_NUMBER,
HIVE_customer_address_2_1546577865912.CA_STREET_NAME,
HIVE_customer_address_2_1546577865912.CA_STREET_TYPE,
HIVE_customer_address_2_1546577865912.CA_SUITE_NUMBER,
HIVE_customer_address_2_1546577865912.CA_CITY,
HIVE_customer_address_2_1546577865912.CA_COUNTY,
HIVE_customer_address_2_1546577865912.CA_STATE,
HIVE_customer_address_2_1546577865912.CA_ZIP,
HIVE_customer_address_2_1546577865912.CA_COUNTRY,
HIVE_customer_address_2_1546577865912.CA_GMT_OFFSET,
HIVE_customer_address_2_1546577865912.CA_LOCATION_TYPE,
POSTGRES_customer_1_1546598025406.C_CUSTOMER_SK,
POSTGRES_customer_1_1546598025406.C_CUSTOMER_ID,
POSTGRES_customer_1_1546598025406.C_CURRENT_CDEMO_SK,
POSTGRES_customer_1_1546598025406.C_CURRENT_HDEMO_SK,
POSTGRES_customer_1_1546598025406.C_CURRENT_ADDR_SK,
POSTGRES_customer_1_1546598025406.C_FIRST_SHIPTO_DATE_SK,
POSTGRES_customer_1_1546598025406.C_FIRST_SALES_DATE_SK,
POSTGRES_customer_1_1546598025406.C_SALUTATION,
POSTGRES_customer_1_1546598025406.C_FIRST_NAME,
POSTGRES_customer_1_1546598025406.C_LAST_NAME,
POSTGRES_customer_1_1546598025406.C_PREFERRED_CUST_FLAG,
POSTGRES_customer_1_1546598025406.C_BIRTH_DAY,
POSTGRES_customer_1_1546598025406.C_BIRTH_MONTH,
POSTGRES_customer_1_1546598025406.C_BIRTH_YEAR,
POSTGRES_customer_1_1546598025406.C_BIRTH_COUNTRY,
POSTGRES_customer_1_1546598025406.C_LOGIN,
POSTGRES_customer_1_1546598025406.C_EMAIL_ADDRESS,
POSTGRES_customer_1_1546598025406.C_LAST_REVIEW_DATE FROM
HIVE_customer_address_2_1546577865912 JOIN POSTGRES_customer_1_1546598025406 ON
HIVE_customer_address_2_1546577865912.ca_address_sk =
POSTGRES_customer_1_1546598025406.c_customer_sk WHERE
POSTGRES_customer_1_1546598025406.CA_ADDRESS_SK IS NOT NULL AND
POSTGRES_customer_1_1546598025406.C_CUSTOMER_SK IS NOT NULL)
[CA_ADDRESS_SK#2399,CA_ADDRESS_ID#2400,CA_STREET_NUMBER#2401,CA_STREET_NAME#2402,CA_STREET_TYPE#2403,CA_SUITE_NUMBER#2404,CA_CITY#2405,CA_COUNTY#2406,CA_STATE#2407,CA_ZIP#2408,CA_COUNTRY#2409,CA_GMT_OFFSET#2410,CA_LOCATION_TYPE#2411,C_CUSTOMER_SK#2519,C_CUSTOMER_ID#2520,C_CURRENT_CDEMO_SK#2521,C_CURRENT_HDEMO_SK#2522,C_CURRENT_ADDR_SK#2523,C_FIRST_SHIPTO_DATE_SK#2524,C_FIRST_SALES_DATE_SK#2525,C_SALUTATION#2526,C_FIRST_NAME#2527,C_LAST_NAME#2528,C_PREFERRED_CUST_FLAG#2529,...
7 more
fields|#2399,CA_ADDRESS_ID#2400,CA_STREET_NUMBER#2401,CA_STREET_NAME#2402,CA_STREET_TYPE#2403,CA_SUITE_NUMBER#2404,CA_CITY#2405,CA_COUNTY#2406,CA_STATE#2407,CA_ZIP#2408,CA_COUNTRY#2409,CA_GMT_OFFSET#2410,CA_LOCATION_TYPE#2411,C_CUSTOMER_SK#2519,C_CUSTOMER_ID#2520,C_CURRENT_CDEMO_SK#2521,C_CURRENT_HDEMO_SK#2522,C_CURRENT_ADDR_SK#2523,C_FIRST_SHIPTO_DATE_SK#2524,C_FIRST_SALES_DATE_SK#2525,C_SALUTATION#2526,C_FIRST_NAME#2527,C_LAST_NAME#2528,C_PREFERRED_CUST_FLAG#2529,...
7 more fields] PushedFilters: [], ReadSchema:
struct<CA_ADDRESS_SK:int,CA_ADDRESS_ID:string,CA_STREET_NUMBER:string,CA_STREET_NAME:string,CA_ST...
> Ignite Spark giving exception when join two cached tables
> ---------------------------------------------------------
>
> Key: IGNITE-10859
> URL: https://issues.apache.org/jira/browse/IGNITE-10859
> Project: Ignite
> Issue Type: Bug
> Components: spark
> Affects Versions: 2.6, 2.7
> Reporter: Ayush
> Priority: Major
>
> When we are loading two data-frames from ignite in spark and joining those to
> dataframes, it is giving exception. We have checked the generated logical
> plan and seems like it is wrong.
> I am adding the stack trace and code
>
> scala> val df1 = spark.read.format(FORMAT_IGNITE).option(OPTION_CONFIG_FILE,
> CONFIG).option(OPTION_TABLE,
> "HIVE_customer_address_2_1546577865912").load().toDF(schema1.columns
> map(_.toLowerCase): _*)
> df1: org.apache.spark.sql.DataFrame = [ca_address_sk: int, ca_address_id:
> string ... 11 more fields]
> scala> df1.show(1)
>
> +----------------+-------------++++------------------------------++-----------------------------++------------------------++--------------++------------------------------------------
> |ca_address_sk|ca_address_id|ca_street_number|ca_street_name|ca_street_type|ca_suite_number|ca_city|ca_county|ca_state|ca_zip|ca_country|ca_gmt_offset|ca_location_type|
> +----------------+-------------++++------------------------------++-----------------------------++------------------------++--------------++------------------------------------------
> |1|AAAAAAAABAAAAAAA|18|Jackson|Parkway|Suite 280|Fairfield|Maricopa
> County|AZ|86192|United States|-7.00|condo|
> +----------------+-------------++++------------------------------++-----------------------------++------------------------++--------------++------------------------------------------
> only showing top 1 row
> scala> val df2 = spark.read.format(FORMAT_IGNITE).option(OPTION_CONFIG_FILE,
> CONFIG).option(OPTION_TABLE,
> "POSTGRES_customer_1_1546598025406").load().toDF(schema2.columns
> map(_.toLowerCase): _*)
> df2: org.apache.spark.sql.DataFrame] = [c_customer_sk: int, c_customer_id:
> string ... 16 more fields]
> scala> df2.show(1)
>
> +----------------+-------------++++------------------------------------++---------------------------------------++---------------------------------++----------------------------------------++--------------------------------++-------------------------++----------------------+--------------------------------------
> |c_customer_sk|c_customer_id|c_current_cdemo_sk|c_current_hdemo_sk|c_current_addr_sk|c_first_shipto_date_sk|c_first_sales_date_sk|c_salutation|c_first_name|c_last_name|c_preferred_cust_flag|c_birth_day|c_birth_month|c_birth_year|c_birth_country|c_login|c_email_address|c_last_review_date|
> +----------------+-------------++++------------------------------------++---------------------------------------++---------------------------------++----------------------------------------++--------------------------------++-------------------------++----------------------+--------------------------------------
> |7288|AAAAAAAAIHMBAAAA|1461725|4938|18198|2450838|2450808|Sir|Steven|Storey
> ...|Y|1|2|1967|QATAR|null|Steven.Storey@QdG...|2452528|
> +----------------+-------------++++------------------------------------++---------------------------------------++---------------------------------++----------------------------------------++--------------------------------++-------------------------++----------------------+--------------------------------------
> scala> df1.join(df2, df1.col("ca_address_sk") === df2.col("c_customer_sk"),
> "inner")
> res64: org.apache.spark.sql.DataFrame = [ca_address_sk: int, ca_address_id:
> string ... 29 more fields]
> scala> res64.show
> 19/01/04 16:50:07 ERROR Executor: Exception in task 0.0 in stage 15.0 (TID
> 15)
> javax.cache.CacheException: Failed to parse query. Column
> "POSTGRES_CUSTOMER_1_1546598025406.CA_ADDRESS_SK" not found; SQL statement:
> SELECT CAST(HIVE_customer_address_2_1546577865912.CA_ADDRESS_SK AS VARCHAR)
> AS ca_address_sk, HIVE_customer_address_2_1546577865912.CA_ADDRESS_ID,
> HIVE_customer_address_2_1546577865912.CA_STREET_NUMBER,
> HIVE_customer_address_2_1546577865912.CA_STREET_NAME,
> HIVE_customer_address_2_1546577865912.CA_STREET_TYPE,
> HIVE_customer_address_2_1546577865912.CA_SUITE_NUMBER,
> HIVE_customer_address_2_1546577865912.CA_CITY,
> HIVE_customer_address_2_1546577865912.CA_COUNTY,
> HIVE_customer_address_2_1546577865912.CA_STATE,
> HIVE_customer_address_2_1546577865912.CA_ZIP,
> HIVE_customer_address_2_1546577865912.CA_COUNTRY,
> CAST(HIVE_customer_address_2_1546577865912.CA_GMT_OFFSET AS VARCHAR) AS
> ca_gmt_offset, HIVE_customer_address_2_1546577865912.CA_LOCATION_TYPE,
> CAST(POSTGRES_customer_1_1546598025406.C_CUSTOMER_SK AS VARCHAR) AS
> c_customer_sk, POSTGRES_customer_1_1546598025406.C_CUSTOMER_ID,
> CAST(POSTGRES_customer_1_1546598025406.C_CURRENT_CDEMO_SK AS VARCHAR) AS
> c_current_cdemo_sk, CAST(POSTGRES_customer_1_1546598025406.C_CURRENT_HDEMO_SK
> AS VARCHAR) AS c_current_hdemo_sk,
> CAST(POSTGRES_customer_1_1546598025406.C_CURRENT_ADDR_SK AS VARCHAR) AS
> c_current_addr_sk,
> CAST(POSTGRES_customer_1_1546598025406.C_FIRST_SHIPTO_DATE_SK AS VARCHAR) AS
> c_first_shipto_date_sk,
> CAST(POSTGRES_customer_1_1546598025406.C_FIRST_SALES_DATE_SK AS VARCHAR) AS
> c_first_sales_date_sk, POSTGRES_customer_1_1546598025406.C_SALUTATION,
> POSTGRES_customer_1_1546598025406.C_FIRST_NAME,
> POSTGRES_customer_1_1546598025406.C_LAST_NAME,
> POSTGRES_customer_1_1546598025406.C_PREFERRED_CUST_FLAG,
> CAST(POSTGRES_customer_1_1546598025406.C_BIRTH_DAY AS VARCHAR) AS
> c_birth_day, CAST(POSTGRES_customer_1_1546598025406.C_BIRTH_MONTH AS VARCHAR)
> AS c_birth_month, CAST(POSTGRES_customer_1_1546598025406.C_BIRTH_YEAR AS
> VARCHAR) AS c_birth_year, POSTGRES_customer_1_1546598025406.C_BIRTH_COUNTRY,
> POSTGRES_customer_1_1546598025406.C_LOGIN,
> POSTGRES_customer_1_1546598025406.C_EMAIL_ADDRESS,
> POSTGRES_customer_1_1546598025406.C_LAST_REVIEW_DATE FROM
> HIVE_customer_address_2_1546577865912 JOIN POSTGRES_customer_1_1546598025406
> ON HIVE_customer_address_2_1546577865912.ca_address_sk =
> POSTGRES_customer_1_1546598025406.c_customer_sk WHERE
> POSTGRES_customer_1_1546598025406.CA_ADDRESS_SK IS NOT NULL AND
> POSTGRES_customer_1_1546598025406.C_CUSTOMER_SK IS NOT NULL LIMIT 21
> [42122-195]
> at
> org.apache.ignite.internal.processors.cache.IgniteCacheProxyImpl.query(IgniteCacheProxyImpl.java:676)
> at
> org.apache.ignite.internal.processors.cache.GatewayProtectedCacheProxy.query(GatewayProtectedCacheProxy.java:344)
> at org.apache.ignite.spark.impl.IgniteSqlRDD.compute(IgniteSqlRDD.scala:37)
> at
> org.apache.ignite.spark.impl.IgniteSQLDataFrameRDD.compute(IgniteSQLDataFrameRDD.scala:74)
> at org.apache.spark.rdd.RDD.computeOrReadCheckpoint(RDD.scala:324)
> at org.apache.spark.rdd.RDD.iterator(RDD.scala:288)
> at org.apache.spark.rdd.MapPartitionsRDD.compute(MapPartitionsRDD.scala:38)
> at org.apache.spark.rdd.RDD.computeOrReadCheckpoint(RDD.scala:324)
> at org.apache.spark.rdd.RDD.iterator(RDD.scala:288)
> at org.apache.spark.rdd.MapPartitionsRDD.compute(MapPartitionsRDD.scala:38)
> at org.apache.spark.rdd.RDD.computeOrReadCheckpoint(RDD.scala:324)
> at org.apache.spark.rdd.RDD.iterator(RDD.scala:288)
> at org.apache.spark.rdd.MapPartitionsRDD.compute(MapPartitionsRDD.scala:38)
> at org.apache.spark.rdd.RDD.computeOrReadCheckpoint(RDD.scala:324)
> at org.apache.spark.rdd.RDD.iterator(RDD.scala:288)
> at org.apache.spark.scheduler.ResultTask.runTask(ResultTask.scala:87)
> at org.apache.spark.scheduler.Task.run(Task.scala:109)
> at org.apache.spark.executor.Executor$TaskRunner.run(Executor.scala:345)
> at
> java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
> at
> java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
> at java.lang.Thread.run(Thread.java:745)
>
>
> //Generated Plan
>
> scala> res67.explain(true)
> == Parsed Logical Plan ==
> Join Inner, (ca_address_sk#2399 = c_customer_sk#2519)
> :- Project [ca_address_sk#2399, ca_address_id#2400, ca_street_number#2401,
> ca_street_name#2402, ca_street_type#2403, ca_suite_number#2404, ca_city#2405,
> ca_county#2406, ca_state#2407, ca_zip#2408, ca_country#2409,
> ca_gmt_offset#2410, ca_location_type#2411|#2399, ca_address_id#2400,
> ca_street_number#2401, ca_street_name#2402, ca_street_type#2403,
> ca_suite_number#2404, ca_city#2405, ca_county#2406, ca_state#2407,
> ca_zip#2408, ca_country#2409, ca_gmt_offset#2410, ca_location_type#2411]
> : +- Project [CA_ADDRESS_SK#2371 AS ca_address_sk#2399, CA_ADDRESS_ID#2372
> AS ca_address_id#2400, CA_STREET_NUMBER#2373 AS ca_street_number#2401,
> CA_STREET_NAME#2374 AS ca_street_name#2402, CA_STREET_TYPE#2375 AS
> ca_street_type#2403, CA_SUITE_NUMBER#2376 AS ca_suite_number#2404,
> CA_CITY#2377 AS ca_city#2405, CA_COUNTY#2378 AS ca_county#2406, CA_STATE#2379
> AS ca_state#2407, CA_ZIP#2380 AS ca_zip#2408, CA_COUNTRY#2381 AS
> ca_country#2409, CA_GMT_OFFSET#2382 AS ca_gmt_offset#2410,
> CA_LOCATION_TYPE#2383 AS ca_location_type#2411, IGNITE_INDEX#2384L AS
> ignite_index#2412L|#2371 AS ca_address_sk#2399, CA_ADDRESS_ID#2372 AS
> ca_address_id#2400, CA_STREET_NUMBER#2373 AS ca_street_number#2401,
> CA_STREET_NAME#2374 AS ca_street_name#2402, CA_STREET_TYPE#2375 AS
> ca_street_type#2403, CA_SUITE_NUMBER#2376 AS ca_suite_number#2404,
> CA_CITY#2377 AS ca_city#2405, CA_COUNTY#2378 AS ca_county#2406, CA_STATE#2379
> AS ca_state#2407, CA_ZIP#2380 AS ca_zip#2408, CA_COUNTRY#2381 AS
> ca_country#2409, CA_GMT_OFFSET#2382 AS ca_gmt_offset#2410,
> CA_LOCATION_TYPE#2383 AS ca_location_type#2411, IGNITE_INDEX#2384L AS
> ignite_index#2412L]
> : +-
> Relation[CA_ADDRESS_SK#2371,CA_ADDRESS_ID#2372,CA_STREET_NUMBER#2373,CA_STREET_NAME#2374,CA_STREET_TYPE#2375,CA_SUITE_NUMBER#2376,CA_CITY#2377,CA_COUNTY#2378,CA_STATE#2379,CA_ZIP#2380,CA_COUNTRY#2381,CA_GMT_OFFSET#2382,CA_LOCATION_TYPE#2383,IGNITE_INDEX#2384L|#2371,CA_ADDRESS_ID#2372,CA_STREET_NUMBER#2373,CA_STREET_NAME#2374,CA_STREET_TYPE#2375,CA_SUITE_NUMBER#2376,CA_CITY#2377,CA_COUNTY#2378,CA_STATE#2379,CA_ZIP#2380,CA_COUNTRY#2381,CA_GMT_OFFSET#2382,CA_LOCATION_TYPE#2383,IGNITE_INDEX#2384L]
> IgniteSQLRelation[table=HIVE_customer_address_2_1546577865912]
> +- Project [c_customer_sk#2519, c_customer_id#2520, c_current_cdemo_sk#2521,
> c_current_hdemo_sk#2522, c_current_addr_sk#2523, c_first_shipto_date_sk#2524,
> c_first_sales_date_sk#2525, c_salutation#2526, c_first_name#2527,
> c_last_name#2528, c_preferred_cust_flag#2529, c_birth_day#2530,
> c_birth_month#2531, c_birth_year#2532, c_birth_country#2533, c_login#2534,
> c_email_address#2535, c_last_review_date#2536|#2519, c_customer_id#2520,
> c_current_cdemo_sk#2521, c_current_hdemo_sk#2522, c_current_addr_sk#2523,
> c_first_shipto_date_sk#2524, c_first_sales_date_sk#2525, c_salutation#2526,
> c_first_name#2527, c_last_name#2528, c_preferred_cust_flag#2529,
> c_birth_day#2530, c_birth_month#2531, c_birth_year#2532,
> c_birth_country#2533, c_login#2534, c_email_address#2535,
> c_last_review_date#2536]
> +- Project [C_CUSTOMER_SK#2481 AS c_customer_sk#2519, C_CUSTOMER_ID#2482 AS
> c_customer_id#2520, C_CURRENT_CDEMO_SK#2483 AS c_current_cdemo_sk#2521,
> C_CURRENT_HDEMO_SK#2484 AS c_current_hdemo_sk#2522, C_CURRENT_ADDR_SK#2485 AS
> c_current_addr_sk#2523, C_FIRST_SHIPTO_DATE_SK#2486 AS
> c_first_shipto_date_sk#2524, C_FIRST_SALES_DATE_SK#2487 AS
> c_first_sales_date_sk#2525, C_SALUTATION#2488 AS c_salutation#2526,
> C_FIRST_NAME#2489 AS c_first_name#2527, C_LAST_NAME#2490 AS c_last_name#2528,
> C_PREFERRED_CUST_FLAG#2491 AS c_preferred_cust_flag#2529, C_BIRTH_DAY#2492 AS
> c_birth_day#2530, C_BIRTH_MONTH#2493 AS c_birth_month#2531, C_BIRTH_YEAR#2494
> AS c_birth_year#2532, C_BIRTH_COUNTRY#2495 AS c_birth_country#2533,
> C_LOGIN#2496 AS c_login#2534, C_EMAIL_ADDRESS#2497 AS c_email_address#2535,
> C_LAST_REVIEW_DATE#2498 AS c_last_review_date#2536, IGNITE_INDEX#2499L AS
> ignite_index#2537L|#2481 AS c_customer_sk#2519, C_CUSTOMER_ID#2482 AS
> c_customer_id#2520, C_CURRENT_CDEMO_SK#2483 AS c_current_cdemo_sk#2521,
> C_CURRENT_HDEMO_SK#2484 AS c_current_hdemo_sk#2522, C_CURRENT_ADDR_SK#2485 AS
> c_current_addr_sk#2523, C_FIRST_SHIPTO_DATE_SK#2486 AS
> c_first_shipto_date_sk#2524, C_FIRST_SALES_DATE_SK#2487 AS
> c_first_sales_date_sk#2525, C_SALUTATION#2488 AS c_salutation#2526,
> C_FIRST_NAME#2489 AS c_first_name#2527, C_LAST_NAME#2490 AS c_last_name#2528,
> C_PREFERRED_CUST_FLAG#2491 AS c_preferred_cust_flag#2529, C_BIRTH_DAY#2492 AS
> c_birth_day#2530, C_BIRTH_MONTH#2493 AS c_birth_month#2531, C_BIRTH_YEAR#2494
> AS c_birth_year#2532, C_BIRTH_COUNTRY#2495 AS c_birth_country#2533,
> C_LOGIN#2496 AS c_login#2534, C_EMAIL_ADDRESS#2497 AS c_email_address#2535,
> C_LAST_REVIEW_DATE#2498 AS c_last_review_date#2536, IGNITE_INDEX#2499L AS
> ignite_index#2537L]
> +-
> Relation[C_CUSTOMER_SK#2481,C_CUSTOMER_ID#2482,C_CURRENT_CDEMO_SK#2483,C_CURRENT_HDEMO_SK#2484,C_CURRENT_ADDR_SK#2485,C_FIRST_SHIPTO_DATE_SK#2486,C_FIRST_SALES_DATE_SK#2487,C_SALUTATION#2488,C_FIRST_NAME#2489,C_LAST_NAME#2490,C_PREFERRED_CUST_FLAG#2491,C_BIRTH_DAY#2492,C_BIRTH_MONTH#2493,C_BIRTH_YEAR#2494,C_BIRTH_COUNTRY#2495,C_LOGIN#2496,C_EMAIL_ADDRESS#2497,C_LAST_REVIEW_DATE#2498,IGNITE_INDEX#2499L|#2481,C_CUSTOMER_ID#2482,C_CURRENT_CDEMO_SK#2483,C_CURRENT_HDEMO_SK#2484,C_CURRENT_ADDR_SK#2485,C_FIRST_SHIPTO_DATE_SK#2486,C_FIRST_SALES_DATE_SK#2487,C_SALUTATION#2488,C_FIRST_NAME#2489,C_LAST_NAME#2490,C_PREFERRED_CUST_FLAG#2491,C_BIRTH_DAY#2492,C_BIRTH_MONTH#2493,C_BIRTH_YEAR#2494,C_BIRTH_COUNTRY#2495,C_LOGIN#2496,C_EMAIL_ADDRESS#2497,C_LAST_REVIEW_DATE#2498,IGNITE_INDEX#2499L]
> IgniteSQLRelation[table=POSTGRES_customer_1_1546598025406]
> == Analyzed Logical Plan ==
> ca_address_sk: int, ca_address_id: string, ca_street_number: string,
> ca_street_name: string, ca_street_type: string, ca_suite_number: string,
> ca_city: string, ca_county: string, ca_state: string, ca_zip: string,
> ca_country: string, ca_gmt_offset: decimal(38,3), ca_location_type: string,
> c_customer_sk: int, c_customer_id: string, c_current_cdemo_sk: int,
> c_current_hdemo_sk: int, c_current_addr_sk: int, c_first_shipto_date_sk: int,
> c_first_sales_date_sk: int, c_salutation: string, c_first_name: string,
> c_last_name: string, c_preferred_cust_flag: string, ... 7 more fields
> Join Inner, (ca_address_sk#2399 = c_customer_sk#2519)
> :- Project [ca_address_sk#2399, ca_address_id#2400, ca_street_number#2401,
> ca_street_name#2402, ca_street_type#2403, ca_suite_number#2404, ca_city#2405,
> ca_county#2406, ca_state#2407, ca_zip#2408, ca_country#2409,
> ca_gmt_offset#2410, ca_location_type#2411|#2399, ca_address_id#2400,
> ca_street_number#2401, ca_street_name#2402, ca_street_type#2403,
> ca_suite_number#2404, ca_city#2405, ca_county#2406, ca_state#2407,
> ca_zip#2408, ca_country#2409, ca_gmt_offset#2410, ca_location_type#2411]
> : +- Project [CA_ADDRESS_SK#2371 AS ca_address_sk#2399, CA_ADDRESS_ID#2372
> AS ca_address_id#2400, CA_STREET_NUMBER#2373 AS ca_street_number#2401,
> CA_STREET_NAME#2374 AS ca_street_name#2402, CA_STREET_TYPE#2375 AS
> ca_street_type#2403, CA_SUITE_NUMBER#2376 AS ca_suite_number#2404,
> CA_CITY#2377 AS ca_city#2405, CA_COUNTY#2378 AS ca_county#2406, CA_STATE#2379
> AS ca_state#2407, CA_ZIP#2380 AS ca_zip#2408, CA_COUNTRY#2381 AS
> ca_country#2409, CA_GMT_OFFSET#2382 AS ca_gmt_offset#2410,
> CA_LOCATION_TYPE#2383 AS ca_location_type#2411, IGNITE_INDEX#2384L AS
> ignite_index#2412L|#2371 AS ca_address_sk#2399, CA_ADDRESS_ID#2372 AS
> ca_address_id#2400, CA_STREET_NUMBER#2373 AS ca_street_number#2401,
> CA_STREET_NAME#2374 AS ca_street_name#2402, CA_STREET_TYPE#2375 AS
> ca_street_type#2403, CA_SUITE_NUMBER#2376 AS ca_suite_number#2404,
> CA_CITY#2377 AS ca_city#2405, CA_COUNTY#2378 AS ca_county#2406, CA_STATE#2379
> AS ca_state#2407, CA_ZIP#2380 AS ca_zip#2408, CA_COUNTRY#2381 AS
> ca_country#2409, CA_GMT_OFFSET#2382 AS ca_gmt_offset#2410,
> CA_LOCATION_TYPE#2383 AS ca_location_type#2411, IGNITE_INDEX#2384L AS
> ignite_index#2412L]
> : +-
> Relation[CA_ADDRESS_SK#2371,CA_ADDRESS_ID#2372,CA_STREET_NUMBER#2373,CA_STREET_NAME#2374,CA_STREET_TYPE#2375,CA_SUITE_NUMBER#2376,CA_CITY#2377,CA_COUNTY#2378,CA_STATE#2379,CA_ZIP#2380,CA_COUNTRY#2381,CA_GMT_OFFSET#2382,CA_LOCATION_TYPE#2383,IGNITE_INDEX#2384L|#2371,CA_ADDRESS_ID#2372,CA_STREET_NUMBER#2373,CA_STREET_NAME#2374,CA_STREET_TYPE#2375,CA_SUITE_NUMBER#2376,CA_CITY#2377,CA_COUNTY#2378,CA_STATE#2379,CA_ZIP#2380,CA_COUNTRY#2381,CA_GMT_OFFSET#2382,CA_LOCATION_TYPE#2383,IGNITE_INDEX#2384L]
> IgniteSQLRelation[table=HIVE_customer_address_2_1546577865912]
> +- Project [c_customer_sk#2519, c_customer_id#2520, c_current_cdemo_sk#2521,
> c_current_hdemo_sk#2522, c_current_addr_sk#2523, c_first_shipto_date_sk#2524,
> c_first_sales_date_sk#2525, c_salutation#2526, c_first_name#2527,
> c_last_name#2528, c_preferred_cust_flag#2529, c_birth_day#2530,
> c_birth_month#2531, c_birth_year#2532, c_birth_country#2533, c_login#2534,
> c_email_address#2535, c_last_review_date#2536|#2519, c_customer_id#2520,
> c_current_cdemo_sk#2521, c_current_hdemo_sk#2522, c_current_addr_sk#2523,
> c_first_shipto_date_sk#2524, c_first_sales_date_sk#2525, c_salutation#2526,
> c_first_name#2527, c_last_name#2528, c_preferred_cust_flag#2529,
> c_birth_day#2530, c_birth_month#2531, c_birth_year#2532,
> c_birth_country#2533, c_login#2534, c_email_address#2535,
> c_last_review_date#2536]
> +- Project [C_CUSTOMER_SK#2481 AS c_customer_sk#2519, C_CUSTOMER_ID#2482 AS
> c_customer_id#2520, C_CURRENT_CDEMO_SK#2483 AS c_current_cdemo_sk#2521,
> C_CURRENT_HDEMO_SK#2484 AS c_current_hdemo_sk#2522, C_CURRENT_ADDR_SK#2485 AS
> c_current_addr_sk#2523, C_FIRST_SHIPTO_DATE_SK#2486 AS
> c_first_shipto_date_sk#2524, C_FIRST_SALES_DATE_SK#2487 AS
> c_first_sales_date_sk#2525, C_SALUTATION#2488 AS c_salutation#2526,
> C_FIRST_NAME#2489 AS c_first_name#2527, C_LAST_NAME#2490 AS c_last_name#2528,
> C_PREFERRED_CUST_FLAG#2491 AS c_preferred_cust_flag#2529, C_BIRTH_DAY#2492 AS
> c_birth_day#2530, C_BIRTH_MONTH#2493 AS c_birth_month#2531, C_BIRTH_YEAR#2494
> AS c_birth_year#2532, C_BIRTH_COUNTRY#2495 AS c_birth_country#2533,
> C_LOGIN#2496 AS c_login#2534, C_EMAIL_ADDRESS#2497 AS c_email_address#2535,
> C_LAST_REVIEW_DATE#2498 AS c_last_review_date#2536, IGNITE_INDEX#2499L AS
> ignite_index#2537L|#2481 AS c_customer_sk#2519, C_CUSTOMER_ID#2482 AS
> c_customer_id#2520, C_CURRENT_CDEMO_SK#2483 AS c_current_cdemo_sk#2521,
> C_CURRENT_HDEMO_SK#2484 AS c_current_hdemo_sk#2522, C_CURRENT_ADDR_SK#2485 AS
> c_current_addr_sk#2523, C_FIRST_SHIPTO_DATE_SK#2486 AS
> c_first_shipto_date_sk#2524, C_FIRST_SALES_DATE_SK#2487 AS
> c_first_sales_date_sk#2525, C_SALUTATION#2488 AS c_salutation#2526,
> C_FIRST_NAME#2489 AS c_first_name#2527, C_LAST_NAME#2490 AS c_last_name#2528,
> C_PREFERRED_CUST_FLAG#2491 AS c_preferred_cust_flag#2529, C_BIRTH_DAY#2492 AS
> c_birth_day#2530, C_BIRTH_MONTH#2493 AS c_birth_month#2531, C_BIRTH_YEAR#2494
> AS c_birth_year#2532, C_BIRTH_COUNTRY#2495 AS c_birth_country#2533,
> C_LOGIN#2496 AS c_login#2534, C_EMAIL_ADDRESS#2497 AS c_email_address#2535,
> C_LAST_REVIEW_DATE#2498 AS c_last_review_date#2536, IGNITE_INDEX#2499L AS
> ignite_index#2537L]
> +-
> Relation[C_CUSTOMER_SK#2481,C_CUSTOMER_ID#2482,C_CURRENT_CDEMO_SK#2483,C_CURRENT_HDEMO_SK#2484,C_CURRENT_ADDR_SK#2485,C_FIRST_SHIPTO_DATE_SK#2486,C_FIRST_SALES_DATE_SK#2487,C_SALUTATION#2488,C_FIRST_NAME#2489,C_LAST_NAME#2490,C_PREFERRED_CUST_FLAG#2491,C_BIRTH_DAY#2492,C_BIRTH_MONTH#2493,C_BIRTH_YEAR#2494,C_BIRTH_COUNTRY#2495,C_LOGIN#2496,C_EMAIL_ADDRESS#2497,C_LAST_REVIEW_DATE#2498,IGNITE_INDEX#2499L|#2481,C_CUSTOMER_ID#2482,C_CURRENT_CDEMO_SK#2483,C_CURRENT_HDEMO_SK#2484,C_CURRENT_ADDR_SK#2485,C_FIRST_SHIPTO_DATE_SK#2486,C_FIRST_SALES_DATE_SK#2487,C_SALUTATION#2488,C_FIRST_NAME#2489,C_LAST_NAME#2490,C_PREFERRED_CUST_FLAG#2491,C_BIRTH_DAY#2492,C_BIRTH_MONTH#2493,C_BIRTH_YEAR#2494,C_BIRTH_COUNTRY#2495,C_LOGIN#2496,C_EMAIL_ADDRESS#2497,C_LAST_REVIEW_DATE#2498,IGNITE_INDEX#2499L]
> IgniteSQLRelation[table=POSTGRES_customer_1_1546598025406]
> == Optimized Logical Plan ==
>
> Relation[CA_ADDRESS_SK#2399,CA_ADDRESS_ID#2400,CA_STREET_NUMBER#2401,CA_STREET_NAME#2402,CA_STREET_TYPE#2403,CA_SUITE_NUMBER#2404,CA_CITY#2405,CA_COUNTY#2406,CA_STATE#2407,CA_ZIP#2408,CA_COUNTRY#2409,CA_GMT_OFFSET#2410,CA_LOCATION_TYPE#2411,C_CUSTOMER_SK#2519,C_CUSTOMER_ID#2520,C_CURRENT_CDEMO_SK#2521,C_CURRENT_HDEMO_SK#2522,C_CURRENT_ADDR_SK#2523,C_FIRST_SHIPTO_DATE_SK#2524,C_FIRST_SALES_DATE_SK#2525,C_SALUTATION#2526,C_FIRST_NAME#2527,C_LAST_NAME#2528,C_PREFERRED_CUST_FLAG#2529,...
> 7 more
> fields|#2399,CA_ADDRESS_ID#2400,CA_STREET_NUMBER#2401,CA_STREET_NAME#2402,CA_STREET_TYPE#2403,CA_SUITE_NUMBER#2404,CA_CITY#2405,CA_COUNTY#2406,CA_STATE#2407,CA_ZIP#2408,CA_COUNTRY#2409,CA_GMT_OFFSET#2410,CA_LOCATION_TYPE#2411,C_CUSTOMER_SK#2519,C_CUSTOMER_ID#2520,C_CURRENT_CDEMO_SK#2521,C_CURRENT_HDEMO_SK#2522,C_CURRENT_ADDR_SK#2523,C_FIRST_SHIPTO_DATE_SK#2524,C_FIRST_SALES_DATE_SK#2525,C_SALUTATION#2526,C_FIRST_NAME#2527,C_LAST_NAME#2528,C_PREFERRED_CUST_FLAG#2529,...
> 7 more fields] IgniteSQLAccumulatorRelation(columns=[CA_ADDRESS_SK,
> CA_ADDRESS_ID, CA_STREET_NUMBER, CA_STREET_NAME, CA_STREET_TYPE,
> CA_SUITE_NUMBER, CA_CITY, CA_COUNTY, CA_STATE, CA_ZIP, CA_COUNTRY,
> CA_GMT_OFFSET, CA_LOCATION_TYPE, C_CUSTOMER_SK, C_CUSTOMER_ID,
> C_CURRENT_CDEMO_SK, C_CURRENT_HDEMO_SK, C_CURRENT_ADDR_SK,
> C_FIRST_SHIPTO_DATE_SK, C_FIRST_SALES_DATE_SK, C_SALUTATION, C_FIRST_NAME,
> C_LAST_NAME, C_PREFERRED_CUST_FLAG, C_BIRTH_DAY, C_BIRTH_MONTH, C_BIRTH_YEAR,
> C_BIRTH_COUNTRY, C_LOGIN, C_EMAIL_ADDRESS, C_LAST_REVIEW_DATE], qry=SELECT
> HIVE_customer_address_2_1546577865912.CA_ADDRESS_SK,
> HIVE_customer_address_2_1546577865912.CA_ADDRESS_ID,
> HIVE_customer_address_2_1546577865912.CA_STREET_NUMBER,
> HIVE_customer_address_2_1546577865912.CA_STREET_NAME,
> HIVE_customer_address_2_1546577865912.CA_STREET_TYPE,
> HIVE_customer_address_2_1546577865912.CA_SUITE_NUMBER,
> HIVE_customer_address_2_1546577865912.CA_CITY,
> HIVE_customer_address_2_1546577865912.CA_COUNTY,
> HIVE_customer_address_2_1546577865912.CA_STATE,
> HIVE_customer_address_2_1546577865912.CA_ZIP,
> HIVE_customer_address_2_1546577865912.CA_COUNTRY,
> HIVE_customer_address_2_1546577865912.CA_GMT_OFFSET,
> HIVE_customer_address_2_1546577865912.CA_LOCATION_TYPE,
> POSTGRES_customer_1_1546598025406.C_CUSTOMER_SK,
> POSTGRES_customer_1_1546598025406.C_CUSTOMER_ID,
> POSTGRES_customer_1_1546598025406.C_CURRENT_CDEMO_SK,
> POSTGRES_customer_1_1546598025406.C_CURRENT_HDEMO_SK,
> POSTGRES_customer_1_1546598025406.C_CURRENT_ADDR_SK,
> POSTGRES_customer_1_1546598025406.C_FIRST_SHIPTO_DATE_SK,
> POSTGRES_customer_1_1546598025406.C_FIRST_SALES_DATE_SK,
> POSTGRES_customer_1_1546598025406.C_SALUTATION,
> POSTGRES_customer_1_1546598025406.C_FIRST_NAME,
> POSTGRES_customer_1_1546598025406.C_LAST_NAME,
> POSTGRES_customer_1_1546598025406.C_PREFERRED_CUST_FLAG,
> POSTGRES_customer_1_1546598025406.C_BIRTH_DAY,
> POSTGRES_customer_1_1546598025406.C_BIRTH_MONTH,
> POSTGRES_customer_1_1546598025406.C_BIRTH_YEAR,
> POSTGRES_customer_1_1546598025406.C_BIRTH_COUNTRY,
> POSTGRES_customer_1_1546598025406.C_LOGIN,
> POSTGRES_customer_1_1546598025406.C_EMAIL_ADDRESS,
> POSTGRES_customer_1_1546598025406.C_LAST_REVIEW_DATE FROM
> HIVE_customer_address_2_1546577865912 JOIN POSTGRES_customer_1_1546598025406
> ON HIVE_customer_address_2_1546577865912.ca_address_sk =
> POSTGRES_customer_1_1546598025406.c_customer_sk WHERE
> POSTGRES_customer_1_1546598025406.CA_ADDRESS_SK IS NOT NULL AND
> POSTGRES_customer_1_1546598025406.C_CUSTOMER_SK IS NOT NULL)
> == Physical Plan ==
> *(1) Scan IgniteSQLAccumulatorRelation(columns=[CA_ADDRESS_SK,
> CA_ADDRESS_ID, CA_STREET_NUMBER, CA_STREET_NAME, CA_STREET_TYPE,
> CA_SUITE_NUMBER, CA_CITY, CA_COUNTY, CA_STATE, CA_ZIP, CA_COUNTRY,
> CA_GMT_OFFSET, CA_LOCATION_TYPE, C_CUSTOMER_SK, C_CUSTOMER_ID,
> C_CURRENT_CDEMO_SK, C_CURRENT_HDEMO_SK, C_CURRENT_ADDR_SK,
> C_FIRST_SHIPTO_DATE_SK, C_FIRST_SALES_DATE_SK, C_SALUTATION, C_FIRST_NAME,
> C_LAST_NAME, C_PREFERRED_CUST_FLAG, C_BIRTH_DAY, C_BIRTH_MONTH, C_BIRTH_YEAR,
> C_BIRTH_COUNTRY, C_LOGIN, C_EMAIL_ADDRESS, C_LAST_REVIEW_DATE], qry=SELECT
> HIVE_customer_address_2_1546577865912.CA_ADDRESS_SK,
> HIVE_customer_address_2_1546577865912.CA_ADDRESS_ID,
> HIVE_customer_address_2_1546577865912.CA_STREET_NUMBER,
> HIVE_customer_address_2_1546577865912.CA_STREET_NAME,
> HIVE_customer_address_2_1546577865912.CA_STREET_TYPE,
> HIVE_customer_address_2_1546577865912.CA_SUITE_NUMBER,
> HIVE_customer_address_2_1546577865912.CA_CITY,
> HIVE_customer_address_2_1546577865912.CA_COUNTY,
> HIVE_customer_address_2_1546577865912.CA_STATE,
> HIVE_customer_address_2_1546577865912.CA_ZIP,
> HIVE_customer_address_2_1546577865912.CA_COUNTRY,
> HIVE_customer_address_2_1546577865912.CA_GMT_OFFSET,
> HIVE_customer_address_2_1546577865912.CA_LOCATION_TYPE,
> POSTGRES_customer_1_1546598025406.C_CUSTOMER_SK,
> POSTGRES_customer_1_1546598025406.C_CUSTOMER_ID,
> POSTGRES_customer_1_1546598025406.C_CURRENT_CDEMO_SK,
> POSTGRES_customer_1_1546598025406.C_CURRENT_HDEMO_SK,
> POSTGRES_customer_1_1546598025406.C_CURRENT_ADDR_SK,
> POSTGRES_customer_1_1546598025406.C_FIRST_SHIPTO_DATE_SK,
> POSTGRES_customer_1_1546598025406.C_FIRST_SALES_DATE_SK,
> POSTGRES_customer_1_1546598025406.C_SALUTATION,
> POSTGRES_customer_1_1546598025406.C_FIRST_NAME,
> POSTGRES_customer_1_1546598025406.C_LAST_NAME,
> POSTGRES_customer_1_1546598025406.C_PREFERRED_CUST_FLAG,
> POSTGRES_customer_1_1546598025406.C_BIRTH_DAY,
> POSTGRES_customer_1_1546598025406.C_BIRTH_MONTH,
> POSTGRES_customer_1_1546598025406.C_BIRTH_YEAR,
> POSTGRES_customer_1_1546598025406.C_BIRTH_COUNTRY,
> POSTGRES_customer_1_1546598025406.C_LOGIN,
> POSTGRES_customer_1_1546598025406.C_EMAIL_ADDRESS,
> POSTGRES_customer_1_1546598025406.C_LAST_REVIEW_DATE FROM
> HIVE_customer_address_2_1546577865912 JOIN POSTGRES_customer_1_1546598025406
> ON HIVE_customer_address_2_1546577865912.ca_address_sk =
> POSTGRES_customer_1_1546598025406.c_customer_sk WHERE
> POSTGRES_customer_1_1546598025406.CA_ADDRESS_SK IS NOT NULL AND
> POSTGRES_customer_1_1546598025406.C_CUSTOMER_SK IS NOT NULL)
> [CA_ADDRESS_SK#2399,CA_ADDRESS_ID#2400,CA_STREET_NUMBER#2401,CA_STREET_NAME#2402,CA_STREET_TYPE#2403,CA_SUITE_NUMBER#2404,CA_CITY#2405,CA_COUNTY#2406,CA_STATE#2407,CA_ZIP#2408,CA_COUNTRY#2409,CA_GMT_OFFSET#2410,CA_LOCATION_TYPE#2411,C_CUSTOMER_SK#2519,C_CUSTOMER_ID#2520,C_CURRENT_CDEMO_SK#2521,C_CURRENT_HDEMO_SK#2522,C_CURRENT_ADDR_SK#2523,C_FIRST_SHIPTO_DATE_SK#2524,C_FIRST_SALES_DATE_SK#2525,C_SALUTATION#2526,C_FIRST_NAME#2527,C_LAST_NAME#2528,C_PREFERRED_CUST_FLAG#2529,...
> 7 more
> fields|#2399,CA_ADDRESS_ID#2400,CA_STREET_NUMBER#2401,CA_STREET_NAME#2402,CA_STREET_TYPE#2403,CA_SUITE_NUMBER#2404,CA_CITY#2405,CA_COUNTY#2406,CA_STATE#2407,CA_ZIP#2408,CA_COUNTRY#2409,CA_GMT_OFFSET#2410,CA_LOCATION_TYPE#2411,C_CUSTOMER_SK#2519,C_CUSTOMER_ID#2520,C_CURRENT_CDEMO_SK#2521,C_CURRENT_HDEMO_SK#2522,C_CURRENT_ADDR_SK#2523,C_FIRST_SHIPTO_DATE_SK#2524,C_FIRST_SALES_DATE_SK#2525,C_SALUTATION#2526,C_FIRST_NAME#2527,C_LAST_NAME#2528,C_PREFERRED_CUST_FLAG#2529,...
> 7 more fields] PushedFilters: [], ReadSchema:
> struct<CA_ADDRESS_SK:int,CA_ADDRESS_ID:string,CA_STREET_NUMBER:string,CA_STREET_NAME:string,CA_ST...
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
