You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@phoenix.apache.org by "apurtell (via GitHub)" <gi...@apache.org> on 2026/06/10 04:45:33 UTC
[PR] PHOENIX-7890 Improve EXPLAIN for joins and unions [phoenix]
apurtell opened a new pull request, #2510:
URL: https://github.com/apache/phoenix/pull/2510
The chosen `JoinCompiler.Strategy` and the `SKIP MERGE` and `DELAYED EVALUATION` decorators move out of inline parentheticals into a single trailing SQL comment per join operator line. `HashJoinPlan` and `SortMergeJoinPlan` gain a `JoinCompiler.Strategy strategy` field and getter/setter. `QueryCompiler.compileJoinQuery` threads the chosen strategy through `HashJoinPlan.create(...)`.
`UnionPlan.getExplainPlan()` is rewritten to compose recursively from each branch's `getExplainPlan()` (like `SortMergeJoinPlan.getExplainPlan()`). The N branches are represented via the existing `subPlans` list. A new `UnionResultIterators.explainBranches(plans, planSteps, builder)` helper is the single source of truth for branch composition. The two `ResultIterators.explain(...)` SPI overrides on `UnionResultIterators` become thin delegates to it. `UnionPlan.getExplainPlan()` no longer triggers sub-plan execution, so connectionless tests can check them.
Minor housekeeping: Replace string 'Truncate Table' with 'TRUNCATE TABLE' in EXPLAIN DDL.
Add TPC-DS derived ITs asserting the full ordered results of various UNION and JOIN plans and expected `assertPlan` assertions.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: issues-unsubscribe@phoenix.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [PR] PHOENIX-7890 Improve EXPLAIN for joins and unions [phoenix]
Posted by "apurtell (via GitHub)" <gi...@apache.org>.
apurtell merged PR #2510:
URL: https://github.com/apache/phoenix/pull/2510
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: issues-unsubscribe@phoenix.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [PR] PHOENIX-7890 Improve EXPLAIN for joins and unions [phoenix]
Posted by "Copilot (via GitHub)" <gi...@apache.org>.
Copilot commented on code in PR #2510:
URL: https://github.com/apache/phoenix/pull/2510#discussion_r3385583718
##########
phoenix-core-client/src/main/java/org/apache/phoenix/iterate/UnionResultIterators.java:
##########
@@ -138,22 +143,39 @@ public List<PeekingResultIterator> getIterators() throws SQLException {
@Override
public void explain(List<String> planSteps,
ExplainPlanAttributesBuilder explainPlanAttributesBuilder) {
- boolean moreThanOneIters = false;
- ExplainPlanAttributesBuilder lhsPointer = null;
- // For more than one iterators, explainPlanAttributes will create
- // chain of objects as lhs and rhs query plans.
- for (PeekingResultIterator iterator : iterators) {
- if (moreThanOneIters) {
- ExplainPlanAttributesBuilder rhsBuilder = new ExplainPlanAttributesBuilder();
- iterator.explain(planSteps, rhsBuilder);
- ExplainPlanAttributes rhsPlans = rhsBuilder.build();
- lhsPointer.setRhsJoinQueryExplainPlan(rhsPlans);
- lhsPointer = rhsBuilder;
- } else {
- iterator.explain(planSteps, explainPlanAttributesBuilder);
- lhsPointer = explainPlanAttributesBuilder;
+ try {
+ explainBranches(plans, planSteps, explainPlanAttributesBuilder);
+ } catch (SQLException e) {
+ throw new RuntimeException(e);
+ }
+ }
+
+ /**
+ * Single source of truth for composing a union's branches into the explain output. Each branch is
+ * rendered from its own {@link QueryPlan#getExplainPlan()} (rather than from its iterator) so the
+ * full sub-plan structure of each branch is preserved, and so explaining a union does not trigger
+ * sub-plan execution. The branch text steps are indented one level and, when a builder is
+ * supplied, the branches' structured attributes are collected onto the builder's {@code subPlans}
+ * list.
+ */
+ public static void explainBranches(List<QueryPlan> plans, List<String> planSteps,
+ ExplainPlanAttributesBuilder builder) throws SQLException {
+ List<ExplainPlanAttributes> subPlans =
+ builder == null ? null : Lists.newArrayListWithExpectedSize(plans.size());
+ for (QueryPlan plan : plans) {
+ ExplainPlan explainPlan = plan.getExplainPlan();
+ for (String step : explainPlan.getPlanSteps()) {
+ planSteps.add(" " + step);
}
- moreThanOneIters = true;
+ if (subPlans != null) {
+ ExplainPlanAttributes attributes = explainPlan.getPlanStepsAsAttributes();
+ if (attributes != null && attributes != ExplainPlanAttributes.getDefaultExplainPlan()) {
+ subPlans.add(attributes);
+ }
+ }
+ }
+ if (subPlans != null && !subPlans.isEmpty()) {
+ builder.setSubPlans(subPlans);
}
Review Comment:
`explainBranches` only adds a branch's structured attributes when the branch's `ExplainPlanAttributes` is not the default singleton. This can silently drop branches from `subPlans` (or leave it unset/null) for plans that only populate textual steps, breaking the intended 1:1 mapping between UNION branches and `subPlans` and potentially causing NPEs for callers expecting `getSubPlans().size() == plans.size()`.
##########
phoenix-core/src/it/java/org/apache/phoenix/end2end/tpcds/TPCDSLikeFixture.java:
##########
@@ -0,0 +1,914 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+package org.apache.phoenix.end2end.tpcds;
+
+import java.sql.Connection;
+import java.sql.PreparedStatement;
+import java.sql.ResultSet;
+import java.sql.SQLException;
+import java.sql.Statement;
+import java.util.Random;
+
+/**
+ * Shared deterministic TPC-DS derived fixture for {@link TPCDSLikeSingleChannelIT} and
+ * {@link TpcdsLikeCrossChannelIT}.
+ * <p>
Review Comment:
The class Javadoc references `{@link TpcdsLikeCrossChannelIT}`, but the actual class name is `TPCDSLikeCrossChannelIT`. The current link is broken and will not resolve in generated Javadoc.
##########
phoenix-core/src/it/java/org/apache/phoenix/end2end/tpcds/TPCDSLikeFixture.java:
##########
@@ -0,0 +1,914 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+package org.apache.phoenix.end2end.tpcds;
+
+import java.sql.Connection;
+import java.sql.PreparedStatement;
+import java.sql.ResultSet;
+import java.sql.SQLException;
+import java.sql.Statement;
+import java.util.Random;
+
+/**
+ * Shared deterministic TPC-DS derived fixture for {@link TPCDSLikeSingleChannelIT} and
+ * {@link TpcdsLikeCrossChannelIT}.
+ * <p>
+ * The schema is a trimmed subset of the TPC-DS v4.0.0 schema, only the tables and columns touched
+ * by the adapted queries, with the following uniform Phoenix adaptation rules applied:
+ * <ul>
+ * <li>Surrogate keys ({@code *_sk}) become single-column {@code BIGINT NOT NULL PRIMARY KEY}
+ * columns on the dimension tables.</li>
+ * <li>Sales and returns fact tables use a composite primary key on
+ * {@code (*_item_sk, *_ticket_number)} or {@code *_order_number}. {@code inventory} uses
+ * {@code (inv_date_sk, inv_item_sk, inv_warehouse_sk)}.</li>
+ * <li>Fact tables are salted ({@code SALT_BUCKETS=4}).</li>
+ * <li>Foreign key columns are plain nullable columns. Referential consistency is guaranteed by this
+ * loader, not by the schema.</li>
+ * </ul>
+ * <p>
+ * The data is generated with a fixed {@link Random} seed so that every adapted query has a stable
+ * reference answer.
+ * <p>
+ * The fixture is materialized into two schemas that hold identical data:
+ * <ul>
+ * <li>{@link #SCHEMA} ({@value #SCHEMA}) -- base tables, no secondary indexes.</li>
+ * <li>{@link #SCHEMA_INDEXED} ({@value #SCHEMA_INDEXED}) -- the same, plus a covering global index
+ * per fact table.</li>
+ * </ul>
+ */
+public final class TPCDSLikeFixture {
+
+ /** Base schema with no secondary indexes */
+ public static final String SCHEMA = "TPCDS";
+ /** Schema holding identical data plus a covering global index per fact table. */
+ public static final String SCHEMA_INDEXED = "TPCDSI";
+
+ private static final long SEED = 42L;
+
+ private static volatile boolean loaded = false;
+
+ private TPCDSLikeFixture() {
+ }
+
+ /** All base (dimension + fact) table short names, in dependency order, dimensions first. */
+ static final String[] TABLES =
+ { "DATE_DIM", "TIME_DIM", "ITEM", "CUSTOMER_ADDRESS", "CUSTOMER_DEMOGRAPHICS", "INCOME_BAND",
+ "HOUSEHOLD_DEMOGRAPHICS", "CUSTOMER", "STORE", "CALL_CENTER", "CATALOG_PAGE", "WEB_SITE",
+ "WEB_PAGE", "WAREHOUSE", "SHIP_MODE", "PROMOTION", "REASON", "STORE_SALES", "STORE_RETURNS",
+ "CATALOG_SALES", "CATALOG_RETURNS", "WEB_SALES", "WEB_RETURNS", "INVENTORY" };
+
+ /** Create both schemas and populate them with deterministic data. */
+ public static synchronized void load(Connection conn) throws SQLException {
+ if (loaded && tablesPopulated(conn)) {
+ return;
+ }
+ conn.setAutoCommit(false);
+ createTables(conn, SCHEMA);
+ createTables(conn, SCHEMA_INDEXED);
+ if (!tablesPopulated(conn)) {
+ loadData(conn, SCHEMA);
+ copyData(conn, SCHEMA, SCHEMA_INDEXED);
+ }
+ createIndexes(conn, SCHEMA_INDEXED);
+ conn.commit();
+ loaded = true;
+ }
+
+ private static boolean tablesPopulated(Connection conn) {
+ try (Statement st = conn.createStatement();
+ ResultSet rs = st.executeQuery("SELECT COUNT(*) FROM " + SCHEMA + ".STORE_SALES")) {
+ return rs.next() && rs.getInt(1) > 0;
+ } catch (SQLException e) {
+ return false;
+ }
+ }
+
+ private static void createTables(Connection conn, String s) throws SQLException {
+ try (Statement st = conn.createStatement()) {
+ for (String ddl : ddl(s)) {
+ st.execute(ddl);
+ }
+ }
+ }
+
+ private static void createIndexes(Connection conn, String s) throws SQLException {
+ try (Statement st = conn.createStatement()) {
+ for (String ddl : indexDdl(s)) {
+ st.execute(ddl);
+ }
+ }
+ }
+
+ private static String[] ddl(String s) {
+ return new String[] {
+ // Dimensions
+ "CREATE TABLE IF NOT EXISTS " + s + ".DATE_DIM (" + "d_date_sk BIGINT NOT NULL PRIMARY KEY,"
+ + "d_date DATE, d_year INTEGER, d_moy INTEGER, d_dom INTEGER, d_qoy INTEGER,"
+ + "d_dow INTEGER, d_week_seq INTEGER, d_day_name VARCHAR(16), d_quarter_name VARCHAR(8))",
+ "CREATE TABLE IF NOT EXISTS " + s + ".TIME_DIM (" + "t_time_sk BIGINT NOT NULL PRIMARY KEY,"
+ + "t_hour INTEGER, t_minute INTEGER, t_am_pm VARCHAR(2), t_shift VARCHAR(16),"
+ + "t_meal_time VARCHAR(16))",
+ "CREATE TABLE IF NOT EXISTS " + s + ".ITEM (" + "i_item_sk BIGINT NOT NULL PRIMARY KEY,"
+ + "i_item_id VARCHAR(16), i_item_desc VARCHAR(100), i_current_price DECIMAL(7,2),"
+ + "i_brand_id INTEGER, i_brand VARCHAR(50), i_class_id INTEGER, i_class VARCHAR(50),"
+ + "i_category_id INTEGER, i_category VARCHAR(50), i_manufact_id INTEGER,"
+ + "i_manufact VARCHAR(50), i_manager_id INTEGER, i_size VARCHAR(20), i_color VARCHAR(20),"
+ + "i_units VARCHAR(10), i_product_name VARCHAR(50))",
+ "CREATE TABLE IF NOT EXISTS " + s + ".CUSTOMER_ADDRESS ("
+ + "ca_address_sk BIGINT NOT NULL PRIMARY KEY, ca_city VARCHAR(60), ca_county VARCHAR(30),"
+ + "ca_state VARCHAR(2), ca_zip VARCHAR(10), ca_country VARCHAR(20),"
+ + "ca_gmt_offset DECIMAL(5,2), ca_street_number VARCHAR(10), ca_street_name VARCHAR(60))",
+ "CREATE TABLE IF NOT EXISTS " + s + ".CUSTOMER_DEMOGRAPHICS ("
+ + "cd_demo_sk BIGINT NOT NULL PRIMARY KEY, cd_gender VARCHAR(1),"
+ + "cd_marital_status VARCHAR(1), cd_education_status VARCHAR(20),"
+ + "cd_purchase_estimate INTEGER, cd_credit_rating VARCHAR(10), cd_dep_count INTEGER,"
+ + "cd_dep_employed_count INTEGER, cd_dep_college_count INTEGER)",
+ "CREATE TABLE IF NOT EXISTS " + s + ".INCOME_BAND ("
+ + "ib_income_band_sk BIGINT NOT NULL PRIMARY KEY, ib_lower_bound INTEGER,"
+ + "ib_upper_bound INTEGER)",
+ "CREATE TABLE IF NOT EXISTS " + s + ".HOUSEHOLD_DEMOGRAPHICS ("
+ + "hd_demo_sk BIGINT NOT NULL PRIMARY KEY, hd_income_band_sk BIGINT,"
+ + "hd_buy_potential VARCHAR(15), hd_dep_count INTEGER, hd_vehicle_count INTEGER)",
+ "CREATE TABLE IF NOT EXISTS " + s + ".CUSTOMER ("
+ + "c_customer_sk BIGINT NOT NULL PRIMARY KEY, c_customer_id VARCHAR(16),"
+ + "c_current_cdemo_sk BIGINT, c_current_hdemo_sk BIGINT, c_current_addr_sk BIGINT,"
+ + "c_first_name VARCHAR(20), c_last_name VARCHAR(30), c_birth_country VARCHAR(20),"
+ + "c_birth_year INTEGER, c_preferred_cust_flag VARCHAR(1), c_salutation VARCHAR(10),"
+ + "c_email_address VARCHAR(50))",
+ "CREATE TABLE IF NOT EXISTS " + s + ".STORE (" + "s_store_sk BIGINT NOT NULL PRIMARY KEY,"
+ + "s_store_id VARCHAR(16), s_store_name VARCHAR(50), s_company_id INTEGER,"
+ + "s_company_name VARCHAR(50), s_state VARCHAR(2), s_zip VARCHAR(10),"
+ + "s_gmt_offset DECIMAL(5,2), s_city VARCHAR(60), s_county VARCHAR(30),"
+ + "s_manager VARCHAR(40), s_number_employees INTEGER)",
+ "CREATE TABLE IF NOT EXISTS " + s + ".CALL_CENTER ("
+ + "cc_call_center_sk BIGINT NOT NULL PRIMARY KEY, cc_call_center_id VARCHAR(16),"
+ + "cc_name VARCHAR(50), cc_manager VARCHAR(40), cc_mkt_id INTEGER, cc_class VARCHAR(50),"
+ + "cc_company INTEGER)",
+ "CREATE TABLE IF NOT EXISTS " + s + ".CATALOG_PAGE ("
+ + "cp_catalog_page_sk BIGINT NOT NULL PRIMARY KEY, cp_catalog_page_id VARCHAR(16),"
+ + "cp_catalog_number INTEGER, cp_catalog_page_number INTEGER, cp_department VARCHAR(50))",
+ "CREATE TABLE IF NOT EXISTS " + s + ".WEB_SITE (" + "web_site_sk BIGINT NOT NULL PRIMARY KEY,"
+ + "web_site_id VARCHAR(16), web_name VARCHAR(50), web_company_id INTEGER,"
+ + "web_company_name VARCHAR(50))",
+ "CREATE TABLE IF NOT EXISTS " + s + ".WEB_PAGE ("
+ + "wp_web_page_sk BIGINT NOT NULL PRIMARY KEY, wp_web_page_id VARCHAR(16),"
+ + "wp_char_count INTEGER, wp_type VARCHAR(50))",
+ "CREATE TABLE IF NOT EXISTS " + s + ".WAREHOUSE ("
+ + "w_warehouse_sk BIGINT NOT NULL PRIMARY KEY, w_warehouse_id VARCHAR(16),"
+ + "w_warehouse_name VARCHAR(50), w_state VARCHAR(2), w_country VARCHAR(20),"
+ + "w_gmt_offset DECIMAL(5,2))",
+ "CREATE TABLE IF NOT EXISTS " + s + ".SHIP_MODE ("
+ + "sm_ship_mode_sk BIGINT NOT NULL PRIMARY KEY, sm_ship_mode_id VARCHAR(16),"
+ + "sm_type VARCHAR(30), sm_carrier VARCHAR(20))",
+ "CREATE TABLE IF NOT EXISTS " + s + ".PROMOTION ("
+ + "p_promo_sk BIGINT NOT NULL PRIMARY KEY, p_promo_id VARCHAR(16),"
+ + "p_channel_email VARCHAR(1), p_channel_event VARCHAR(1), p_channel_tv VARCHAR(1),"
+ + "p_channel_dmail VARCHAR(1), p_channel_catalog VARCHAR(1))",
+ "CREATE TABLE IF NOT EXISTS " + s + ".REASON (" + "r_reason_sk BIGINT NOT NULL PRIMARY KEY,"
+ + "r_reason_id VARCHAR(16), r_reason_desc VARCHAR(100))",
+ // Facts
+ "CREATE TABLE IF NOT EXISTS " + s + ".STORE_SALES (" + "ss_item_sk BIGINT NOT NULL,"
+ + "ss_ticket_number BIGINT NOT NULL, ss_sold_date_sk BIGINT, ss_sold_time_sk BIGINT,"
+ + "ss_customer_sk BIGINT, ss_cdemo_sk BIGINT, ss_hdemo_sk BIGINT, ss_addr_sk BIGINT,"
+ + "ss_store_sk BIGINT, ss_promo_sk BIGINT, ss_quantity INTEGER, ss_sales_price DECIMAL(7,2),"
+ + "ss_ext_sales_price DECIMAL(7,2), ss_ext_discount_amt DECIMAL(7,2),"
+ + "ss_list_price DECIMAL(7,2), ss_ext_list_price DECIMAL(7,2), ss_coupon_amt DECIMAL(7,2),"
+ + "ss_net_profit DECIMAL(7,2), ss_net_paid DECIMAL(7,2) "
+ + "CONSTRAINT pk PRIMARY KEY (ss_item_sk, ss_ticket_number)) SALT_BUCKETS=4",
+ "CREATE TABLE IF NOT EXISTS " + s + ".STORE_RETURNS (" + "sr_item_sk BIGINT NOT NULL,"
+ + "sr_ticket_number BIGINT NOT NULL, sr_returned_date_sk BIGINT, sr_customer_sk BIGINT,"
+ + "sr_cdemo_sk BIGINT, sr_hdemo_sk BIGINT, sr_addr_sk BIGINT, sr_store_sk BIGINT,"
+ + "sr_reason_sk BIGINT, sr_return_quantity INTEGER, sr_return_amt DECIMAL(7,2),"
+ + "sr_net_loss DECIMAL(7,2), sr_fee DECIMAL(7,2) "
+ + "CONSTRAINT pk PRIMARY KEY (sr_item_sk, sr_ticket_number)) SALT_BUCKETS=4",
+ "CREATE TABLE IF NOT EXISTS " + s + ".CATALOG_SALES (" + "cs_item_sk BIGINT NOT NULL,"
+ + "cs_order_number BIGINT NOT NULL, cs_sold_date_sk BIGINT, cs_bill_customer_sk BIGINT,"
+ + "cs_bill_cdemo_sk BIGINT, cs_ship_customer_sk BIGINT, cs_ship_addr_sk BIGINT,"
+ + "cs_call_center_sk BIGINT, cs_catalog_page_sk BIGINT, cs_warehouse_sk BIGINT,"
+ + "cs_ship_mode_sk BIGINT, cs_promo_sk BIGINT, cs_quantity INTEGER,"
+ + "cs_sales_price DECIMAL(7,2), cs_ext_sales_price DECIMAL(7,2),"
+ + "cs_ext_discount_amt DECIMAL(7,2), cs_list_price DECIMAL(7,2),"
+ + "cs_ext_list_price DECIMAL(7,2), cs_coupon_amt DECIMAL(7,2), cs_net_profit DECIMAL(7,2),"
+ + "cs_net_paid DECIMAL(7,2) "
+ + "CONSTRAINT pk PRIMARY KEY (cs_item_sk, cs_order_number)) SALT_BUCKETS=4",
+ "CREATE TABLE IF NOT EXISTS " + s + ".CATALOG_RETURNS (" + "cr_item_sk BIGINT NOT NULL,"
+ + "cr_order_number BIGINT NOT NULL, cr_returned_date_sk BIGINT,"
+ + "cr_returning_customer_sk BIGINT, cr_call_center_sk BIGINT, cr_catalog_page_sk BIGINT,"
+ + "cr_warehouse_sk BIGINT, cr_reason_sk BIGINT, cr_return_quantity INTEGER,"
+ + "cr_return_amount DECIMAL(7,2), cr_net_loss DECIMAL(7,2) "
+ + "CONSTRAINT pk PRIMARY KEY (cr_item_sk, cr_order_number)) SALT_BUCKETS=4",
+ "CREATE TABLE IF NOT EXISTS " + s + ".WEB_SALES (" + "ws_item_sk BIGINT NOT NULL,"
+ + "ws_order_number BIGINT NOT NULL, ws_sold_date_sk BIGINT, ws_sold_time_sk BIGINT,"
+ + "ws_bill_customer_sk BIGINT, ws_ship_customer_sk BIGINT, ws_ship_addr_sk BIGINT,"
+ + "ws_web_site_sk BIGINT, ws_web_page_sk BIGINT, ws_warehouse_sk BIGINT,"
+ + "ws_ship_mode_sk BIGINT, ws_promo_sk BIGINT, ws_quantity INTEGER,"
+ + "ws_sales_price DECIMAL(7,2), ws_ext_sales_price DECIMAL(7,2),"
+ + "ws_ext_discount_amt DECIMAL(7,2), ws_list_price DECIMAL(7,2),"
+ + "ws_ext_list_price DECIMAL(7,2), ws_coupon_amt DECIMAL(7,2), ws_net_profit DECIMAL(7,2),"
+ + "ws_net_paid DECIMAL(7,2) "
+ + "CONSTRAINT pk PRIMARY KEY (ws_item_sk, ws_order_number)) SALT_BUCKETS=4",
+ "CREATE TABLE IF NOT EXISTS " + s + ".WEB_RETURNS (" + "wr_item_sk BIGINT NOT NULL,"
+ + "wr_order_number BIGINT NOT NULL, wr_returned_date_sk BIGINT,"
+ + "wr_returning_customer_sk BIGINT, wr_web_page_sk BIGINT, wr_reason_sk BIGINT,"
+ + "wr_return_quantity INTEGER, wr_return_amt DECIMAL(7,2), wr_net_loss DECIMAL(7,2) "
+ + "CONSTRAINT pk PRIMARY KEY (wr_item_sk, wr_order_number)) SALT_BUCKETS=4",
+ "CREATE TABLE IF NOT EXISTS " + s + ".INVENTORY (" + "inv_date_sk BIGINT NOT NULL,"
+ + "inv_item_sk BIGINT NOT NULL, inv_warehouse_sk BIGINT NOT NULL,"
+ + "inv_quantity_on_hand INTEGER "
+ + "CONSTRAINT pk PRIMARY KEY (inv_date_sk, inv_item_sk, inv_warehouse_sk)) SALT_BUCKETS=4" };
+ }
+
+ private static String[] indexDdl(String s) {
+ String suffix = "_I";
+ return new String[] {
+ "CREATE INDEX IF NOT EXISTS SS" + suffix + " ON " + s
+ + ".STORE_SALES (ss_sold_date_sk, ss_item_sk) INCLUDE (ss_store_sk, ss_customer_sk,"
+ + " ss_quantity, ss_sales_price, ss_ext_sales_price, ss_ext_discount_amt, ss_list_price,"
+ + " ss_net_profit, ss_net_paid)",
+ "CREATE INDEX IF NOT EXISTS CS" + suffix + " ON " + s
+ + ".CATALOG_SALES (cs_sold_date_sk, cs_item_sk) INCLUDE (cs_bill_customer_sk,"
+ + " cs_warehouse_sk, cs_quantity, cs_sales_price, cs_ext_sales_price, cs_ext_discount_amt,"
+ + " cs_list_price, cs_net_profit, cs_net_paid)",
+ "CREATE INDEX IF NOT EXISTS WS" + suffix + " ON " + s
+ + ".WEB_SALES (ws_sold_date_sk, ws_item_sk) INCLUDE (ws_bill_customer_sk, ws_warehouse_sk,"
+ + " ws_quantity, ws_sales_price, ws_ext_sales_price, ws_ext_discount_amt, ws_list_price,"
+ + " ws_net_profit, ws_net_paid)",
+ "CREATE INDEX IF NOT EXISTS INV" + suffix + " ON " + s
+ + ".INVENTORY (inv_item_sk, inv_date_sk) INCLUDE (inv_warehouse_sk, inv_quantity_on_hand)" };
+ }
+
+ static final int[] YEARS = { 2000, 2001 };
+ static final String[] CATEGORIES =
+ { "Books", "Home", "Electronics", "Sports", "Jewelry", "Music" };
+ static final String[] STATES = { "CA", "TX", "NY", "WA", "OR" };
+ static final String[] GENDERS = { "M", "F" };
+ static final String[] MARITAL = { "M", "S", "D", "W", "U" };
+ static final String[] EDUCATION =
+ { "Primary", "Secondary", "College", "2 yr Degree", "4 yr Degree", "Advanced Degree" };
+ static final String[] BUY_POTENTIAL =
+ { ">10000", "5001-10000", "1001-5000", "501-1000", "0-500", "Unknown" };
+ static final String[] DAY_NAMES =
+ { "Sunday", "Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday" };
+ static final double[] GMT_OFFSETS = { -5, -6, -7, -8 };
+
+ private static final int N_ITEMS = 24;
+ private static final int N_CUSTOMERS = 30;
+ private static final int N_ADDR = 20;
+ private static final int N_CDEMO = 12;
+ private static final int N_HDEMO = 12;
+ private static final int N_STORES = 6;
+ private static final int N_CALL_CENTERS = 4;
+ private static final int N_CATALOG_PAGES = 6;
+ private static final int N_WEB_SITES = 4;
+ private static final int N_WEB_PAGES = 6;
+ private static final int N_WAREHOUSES = 4;
+ private static final int N_SHIP_MODES = 5;
+ private static final int N_PROMOS = 10;
+ private static final int N_REASONS = 6;
+
+ // date_dim: 24 rows = 12 months x 2 years, d_date_sk = 1..24.
+ private static final int N_DATES = YEARS.length * 12;
+ private static final int N_TIMES = 12;
+
+ private static void loadData(Connection conn, String s) throws SQLException {
+ Random rnd = new Random(SEED);
+ loadDateDim(conn, s);
+ loadTimeDim(conn, s);
+ loadItem(conn, s, rnd);
+ loadCustomerAddress(conn, s, rnd);
+ loadCustomerDemographics(conn, s);
+ loadIncomeBand(conn, s);
+ loadHouseholdDemographics(conn, s, rnd);
+ loadCustomer(conn, s, rnd);
+ loadStore(conn, s, rnd);
+ loadCallCenter(conn, s);
+ loadCatalogPage(conn, s);
+ loadWebSite(conn, s);
+ loadWebPage(conn, s);
+ loadWarehouse(conn, s, rnd);
+ loadShipMode(conn, s);
+ loadPromotion(conn, s, rnd);
+ loadReason(conn, s);
+ conn.commit();
+ loadStoreSales(conn, s, rnd);
+ loadStoreReturns(conn, s, rnd);
+ loadCatalogSales(conn, s, rnd);
+ loadCatalogReturns(conn, s, rnd);
+ loadWebSales(conn, s, rnd);
+ loadWebReturns(conn, s, rnd);
+ loadInventory(conn, s, rnd);
+ conn.commit();
+ }
+
+ private static void copyData(Connection conn, String from, String to) throws SQLException {
+ try (Statement st = conn.createStatement()) {
+ for (String t : TABLES) {
+ st.executeUpdate("UPSERT INTO " + to + "." + t + " SELECT * FROM " + from + "." + t);
+ }
+ }
+ conn.commit();
+ }
+
+ private static void loadDateDim(Connection conn, String s) throws SQLException {
+ String sql = "UPSERT INTO " + s + ".DATE_DIM (d_date_sk, d_date, d_year, d_moy, d_dom, d_qoy,"
+ + " d_dow, d_week_seq, d_day_name, d_quarter_name) VALUES (?,?,?,?,?,?,?,?,?,?)";
+ try (PreparedStatement ps = conn.prepareStatement(sql)) {
+ int sk = 0;
+ for (int yi = 0; yi < YEARS.length; yi++) {
+ int year = YEARS[yi];
+ for (int moy = 1; moy <= 12; moy++) {
+ sk++;
+ int dom = 15;
+ int qoy = (moy - 1) / 3 + 1;
+ int dow = (sk % 7);
+ int weekSeq = (year - 2000) * 52 + moy * 4;
+ ps.setLong(1, sk);
+ ps.setDate(2, java.sql.Date.valueOf(String.format("%04d-%02d-%02d", year, moy, dom)));
+ ps.setInt(3, year);
+ ps.setInt(4, moy);
+ ps.setInt(5, dom);
+ ps.setInt(6, qoy);
+ ps.setInt(7, dow);
+ ps.setInt(8, weekSeq);
+ ps.setString(9, DAY_NAMES[dow]);
+ ps.setString(10, year + "Q" + qoy);
+ ps.executeUpdate();
+ }
+ }
+ }
+ }
+
+ private static void loadTimeDim(Connection conn, String s) throws SQLException {
+ String sql = "UPSERT INTO " + s + ".TIME_DIM (t_time_sk, t_hour, t_minute, t_am_pm, t_shift,"
+ + " t_meal_time) VALUES (?,?,?,?,?,?)";
+ try (PreparedStatement ps = conn.prepareStatement(sql)) {
+ for (int i = 1; i <= N_TIMES; i++) {
+ int hour = (i * 2) % 24;
+ ps.setLong(1, i);
+ ps.setInt(2, hour);
+ ps.setInt(3, 0);
+ ps.setString(4, hour < 12 ? "AM" : "PM");
+ ps.setString(5, hour < 8 ? "third" : hour < 16 ? "first" : "second");
+ ps.setString(6,
+ hour >= 6 && hour < 10 ? "breakfast"
+ : hour >= 11 && hour < 14 ? "lunch"
+ : hour >= 17 && hour < 21 ? "dinner"
+ : null);
+ ps.executeUpdate();
+ }
+ }
+ }
+
+ private static void loadItem(Connection conn, String s, Random rnd) throws SQLException {
+ String sql = "UPSERT INTO " + s + ".ITEM (i_item_sk, i_item_id, i_item_desc, i_current_price,"
+ + " i_brand_id, i_brand, i_class_id, i_class, i_category_id, i_category, i_manufact_id,"
+ + " i_manufact, i_manager_id, i_size, i_color, i_units, i_product_name)"
+ + " VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
+ String[] sizes = { "small", "medium", "large", "petite", "economy", "N/A" };
+ String[] colors = { "red", "blue", "green", "white", "black", "almond" };
+ String[] units = { "Each", "Dozen", "Case", "Box", "Pallet" };
+ try (PreparedStatement ps = conn.prepareStatement(sql)) {
+ for (int i = 1; i <= N_ITEMS; i++) {
+ int catId = (i - 1) % CATEGORIES.length;
+ int classId = (i - 1) % 5 + 1;
+ int brandId = 1000 + ((i - 1) % 8);
+ int manufactId = (i - 1) % 5 + 1;
+ int managerId = (i - 1) % 10 + 1;
+ ps.setLong(1, i);
+ ps.setString(2, String.format("ITEM%010d", i));
+ ps.setString(3, "Item description " + i);
+ ps.setBigDecimal(4, bd(10 + (i % 90) + 0.99));
+ ps.setInt(5, brandId);
+ ps.setString(6, "brand#" + brandId);
+ ps.setInt(7, classId);
+ ps.setString(8, "class#" + classId);
+ ps.setInt(9, catId + 1);
+ ps.setString(10, CATEGORIES[catId]);
+ ps.setInt(11, manufactId);
+ ps.setString(12, "manufact#" + manufactId);
+ ps.setInt(13, managerId);
+ ps.setString(14, sizes[i % sizes.length]);
+ ps.setString(15, colors[i % colors.length]);
+ ps.setString(16, units[i % units.length]);
+ ps.setString(17, "product#" + i);
+ ps.executeUpdate();
+ }
+ }
+ }
+
+ private static void loadCustomerAddress(Connection conn, String s, Random rnd)
+ throws SQLException {
+ String sql = "UPSERT INTO " + s + ".CUSTOMER_ADDRESS (ca_address_sk, ca_city, ca_county,"
+ + " ca_state, ca_zip, ca_country, ca_gmt_offset, ca_street_number, ca_street_name)"
+ + " VALUES (?,?,?,?,?,?,?,?,?)";
+ try (PreparedStatement ps = conn.prepareStatement(sql)) {
+ for (int i = 1; i <= N_ADDR; i++) {
+ String state = STATES[(i - 1) % STATES.length];
+ ps.setLong(1, i);
+ ps.setString(2, "City" + ((i - 1) % 7));
+ ps.setString(3, "County" + ((i - 1) % 4));
+ ps.setString(4, state);
+ ps.setString(5, String.format("%05d", 10000 + i));
+ ps.setString(6, "United States");
+ ps.setBigDecimal(7, bd(GMT_OFFSETS[(i - 1) % GMT_OFFSETS.length]));
+ ps.setString(8, Integer.toString(100 + i));
+ ps.setString(9, "Street " + i);
+ ps.executeUpdate();
+ }
+ }
+ }
+
+ private static void loadCustomerDemographics(Connection conn, String s) throws SQLException {
+ String sql = "UPSERT INTO " + s + ".CUSTOMER_DEMOGRAPHICS (cd_demo_sk, cd_gender,"
+ + " cd_marital_status, cd_education_status, cd_purchase_estimate, cd_credit_rating,"
+ + " cd_dep_count, cd_dep_employed_count, cd_dep_college_count) VALUES (?,?,?,?,?,?,?,?,?)";
+ String[] credit = { "Low Risk", "Good", "High Risk", "Unknown" };
+ try (PreparedStatement ps = conn.prepareStatement(sql)) {
+ for (int i = 1; i <= N_CDEMO; i++) {
+ ps.setLong(1, i);
+ ps.setString(2, GENDERS[(i - 1) % GENDERS.length]);
+ ps.setString(3, MARITAL[(i - 1) % MARITAL.length]);
+ ps.setString(4, EDUCATION[(i - 1) % EDUCATION.length]);
+ ps.setInt(5, 500 * (((i - 1) % 6) + 1));
+ ps.setString(6, credit[(i - 1) % credit.length]);
+ ps.setInt(7, (i - 1) % 4);
+ ps.setInt(8, (i - 1) % 3);
+ ps.setInt(9, (i - 1) % 2);
+ ps.executeUpdate();
+ }
+ }
+ }
+
+ private static void loadIncomeBand(Connection conn, String s) throws SQLException {
+ String sql = "UPSERT INTO " + s + ".INCOME_BAND (ib_income_band_sk, ib_lower_bound,"
+ + " ib_upper_bound) VALUES (?,?,?)";
+ try (PreparedStatement ps = conn.prepareStatement(sql)) {
+ for (int i = 1; i <= 10; i++) {
+ ps.setLong(1, i);
+ ps.setInt(2, (i - 1) * 10000);
+ ps.setInt(3, i * 10000);
+ ps.executeUpdate();
+ }
+ }
+ }
+
+ private static void loadHouseholdDemographics(Connection conn, String s, Random rnd)
+ throws SQLException {
+ String sql = "UPSERT INTO " + s + ".HOUSEHOLD_DEMOGRAPHICS (hd_demo_sk, hd_income_band_sk,"
+ + " hd_buy_potential, hd_dep_count, hd_vehicle_count) VALUES (?,?,?,?,?)";
+ try (PreparedStatement ps = conn.prepareStatement(sql)) {
+ for (int i = 1; i <= N_HDEMO; i++) {
+ ps.setLong(1, i);
+ ps.setLong(2, ((i - 1) % 10) + 1);
+ ps.setString(3, BUY_POTENTIAL[(i - 1) % BUY_POTENTIAL.length]);
+ ps.setInt(4, (i - 1) % 5);
+ ps.setInt(5, (i - 1) % 4);
+ ps.executeUpdate();
+ }
+ }
+ }
+
+ private static void loadCustomer(Connection conn, String s, Random rnd) throws SQLException {
+ String sql = "UPSERT INTO " + s + ".CUSTOMER (c_customer_sk, c_customer_id, c_current_cdemo_sk,"
+ + " c_current_hdemo_sk, c_current_addr_sk, c_first_name, c_last_name, c_birth_country,"
+ + " c_birth_year, c_preferred_cust_flag, c_salutation, c_email_address)"
+ + " VALUES (?,?,?,?,?,?,?,?,?,?,?,?)";
+ String[] countries = { "UNITED STATES", "CANADA", "MEXICO", "BRAZIL" };
+ try (PreparedStatement ps = conn.prepareStatement(sql)) {
+ for (int i = 1; i <= N_CUSTOMERS; i++) {
+ ps.setLong(1, i);
+ ps.setString(2, String.format("CUST%012d", i));
+ ps.setLong(3, ((i - 1) % N_CDEMO) + 1);
+ ps.setLong(4, ((i - 1) % N_HDEMO) + 1);
+ ps.setLong(5, ((i - 1) % N_ADDR) + 1);
+ ps.setString(6, "First" + i);
+ ps.setString(7, "Last" + i);
+ ps.setString(8, countries[(i - 1) % countries.length]);
+ ps.setInt(9, 1950 + (i % 40));
+ ps.setString(10, (i % 2 == 0) ? "Y" : "N");
+ ps.setString(11, (i % 2 == 0) ? "Mr." : "Ms.");
+ ps.setString(12, "cust" + i + "@example.com");
+ ps.executeUpdate();
+ }
+ }
+ }
+
+ private static void loadStore(Connection conn, String s, Random rnd) throws SQLException {
+ String sql = "UPSERT INTO " + s + ".STORE (s_store_sk, s_store_id, s_store_name, s_company_id,"
+ + " s_company_name, s_state, s_zip, s_gmt_offset, s_city, s_county, s_manager,"
+ + " s_number_employees) VALUES (?,?,?,?,?,?,?,?,?,?,?,?)";
+ try (PreparedStatement ps = conn.prepareStatement(sql)) {
+ for (int i = 1; i <= N_STORES; i++) {
+ String state = STATES[(i - 1) % STATES.length];
+ ps.setLong(1, i);
+ ps.setString(2, String.format("STORE%010d", i));
+ ps.setString(3, "Store " + i);
+ ps.setInt(4, 1);
+ ps.setString(5, "company#1");
+ ps.setString(6, state);
+ ps.setString(7, String.format("%05d", 20000 + i));
+ ps.setBigDecimal(8, bd(GMT_OFFSETS[(i - 1) % GMT_OFFSETS.length]));
+ ps.setString(9, "City" + ((i - 1) % 7));
+ ps.setString(10, "County" + ((i - 1) % 4));
+ ps.setString(11, "Manager" + i);
+ ps.setInt(12, 100 + i * 10);
+ ps.executeUpdate();
+ }
+ }
+ }
+
+ private static void loadCallCenter(Connection conn, String s) throws SQLException {
+ String sql = "UPSERT INTO " + s + ".CALL_CENTER (cc_call_center_sk, cc_call_center_id, cc_name,"
+ + " cc_manager, cc_mkt_id, cc_class, cc_company) VALUES (?,?,?,?,?,?,?)";
+ String[] classes = { "small", "medium", "large" };
+ try (PreparedStatement ps = conn.prepareStatement(sql)) {
+ for (int i = 1; i <= N_CALL_CENTERS; i++) {
+ ps.setLong(1, i);
+ ps.setString(2, String.format("CC%014d", i));
+ ps.setString(3, "Call Center " + i);
+ ps.setString(4, "CCManager" + i);
+ ps.setInt(5, (i - 1) % 3 + 1);
+ ps.setString(6, classes[(i - 1) % classes.length]);
+ ps.setInt(7, 1);
+ ps.executeUpdate();
+ }
+ }
+ }
+
+ private static void loadCatalogPage(Connection conn, String s) throws SQLException {
+ String sql = "UPSERT INTO " + s + ".CATALOG_PAGE (cp_catalog_page_sk, cp_catalog_page_id,"
+ + " cp_catalog_number, cp_catalog_page_number, cp_department) VALUES (?,?,?,?,?)";
+ try (PreparedStatement ps = conn.prepareStatement(sql)) {
+ for (int i = 1; i <= N_CATALOG_PAGES; i++) {
+ ps.setLong(1, i);
+ ps.setString(2, String.format("CP%014d", i));
+ ps.setInt(3, (i - 1) % 3 + 1);
+ ps.setInt(4, i);
+ ps.setString(5, "DEPARTMENT");
+ ps.executeUpdate();
+ }
+ }
+ }
+
+ private static void loadWebSite(Connection conn, String s) throws SQLException {
+ String sql = "UPSERT INTO " + s + ".WEB_SITE (web_site_sk, web_site_id, web_name,"
+ + " web_company_id, web_company_name) VALUES (?,?,?,?,?)";
+ try (PreparedStatement ps = conn.prepareStatement(sql)) {
+ for (int i = 1; i <= N_WEB_SITES; i++) {
+ ps.setLong(1, i);
+ ps.setString(2, String.format("WS%014d", i));
+ ps.setString(3, "Web Site " + i);
+ ps.setInt(4, 1);
+ ps.setString(5, "company#1");
+ ps.executeUpdate();
+ }
+ }
+ }
+
+ private static void loadWebPage(Connection conn, String s) throws SQLException {
+ String sql = "UPSERT INTO " + s + ".WEB_PAGE (wp_web_page_sk, wp_web_page_id, wp_char_count,"
+ + " wp_type) VALUES (?,?,?,?)";
+ String[] types = { "welcome", "protected", "feedback", "general", "ad", "order" };
+ try (PreparedStatement ps = conn.prepareStatement(sql)) {
+ for (int i = 1; i <= N_WEB_PAGES; i++) {
+ ps.setLong(1, i);
+ ps.setString(2, String.format("WP%014d", i));
+ ps.setInt(3, 1000 + i * 100);
+ ps.setString(4, types[(i - 1) % types.length]);
+ ps.executeUpdate();
+ }
+ }
+ }
+
+ private static void loadWarehouse(Connection conn, String s, Random rnd) throws SQLException {
+ String sql = "UPSERT INTO " + s + ".WAREHOUSE (w_warehouse_sk, w_warehouse_id,"
+ + " w_warehouse_name, w_state, w_country, w_gmt_offset) VALUES (?,?,?,?,?,?)";
+ try (PreparedStatement ps = conn.prepareStatement(sql)) {
+ for (int i = 1; i <= N_WAREHOUSES; i++) {
+ ps.setLong(1, i);
+ ps.setString(2, String.format("WH%014d", i));
+ ps.setString(3, "Warehouse " + i);
+ ps.setString(4, STATES[(i - 1) % STATES.length]);
+ ps.setString(5, "United States");
+ ps.setBigDecimal(6, bd(GMT_OFFSETS[(i - 1) % GMT_OFFSETS.length]));
+ ps.executeUpdate();
+ }
+ }
+ }
+
+ private static void loadShipMode(Connection conn, String s) throws SQLException {
+ String sql = "UPSERT INTO " + s + ".SHIP_MODE (sm_ship_mode_sk, sm_ship_mode_id, sm_type,"
+ + " sm_carrier) VALUES (?,?,?,?)";
+ String[] types = { "EXPRESS", "NEXT DAY", "OVERNIGHT", "TWO DAY", "LIBRARY" };
+ String[] carriers = { "DHL", "FEDEX", "UPS", "USPS", "ZHOU" };
+ try (PreparedStatement ps = conn.prepareStatement(sql)) {
+ for (int i = 1; i <= N_SHIP_MODES; i++) {
+ ps.setLong(1, i);
+ ps.setString(2, String.format("SM%014d", i));
+ ps.setString(3, types[(i - 1) % types.length]);
+ ps.setString(4, carriers[(i - 1) % carriers.length]);
+ ps.executeUpdate();
+ }
+ }
+ }
+
+ private static void loadPromotion(Connection conn, String s, Random rnd) throws SQLException {
+ String sql = "UPSERT INTO " + s + ".PROMOTION (p_promo_sk, p_promo_id, p_channel_email,"
+ + " p_channel_event, p_channel_tv, p_channel_dmail, p_channel_catalog) VALUES (?,?,?,?,?,?,?)";
+ try (PreparedStatement ps = conn.prepareStatement(sql)) {
+ for (int i = 1; i <= N_PROMOS; i++) {
+ ps.setLong(1, i);
+ ps.setString(2, String.format("PROMO%010d", i));
+ ps.setString(3, (i % 2 == 0) ? "Y" : "N");
+ ps.setString(4, (i % 3 == 0) ? "Y" : "N");
+ ps.setString(5, (i % 2 == 1) ? "Y" : "N");
+ ps.setString(6, (i % 4 == 0) ? "Y" : "N");
+ ps.setString(7, (i % 5 == 0) ? "Y" : "N");
+ ps.executeUpdate();
+ }
+ }
+ }
+
+ private static void loadReason(Connection conn, String s) throws SQLException {
+ String sql =
+ "UPSERT INTO " + s + ".REASON (r_reason_sk, r_reason_id, r_reason_desc)" + " VALUES (?,?,?)";
+ try (PreparedStatement ps = conn.prepareStatement(sql)) {
+ for (int i = 1; i <= N_REASONS; i++) {
+ ps.setLong(1, i);
+ ps.setString(2, String.format("REASON%010d", i));
+ ps.setString(3, "Reason " + i);
+ ps.executeUpdate();
+ }
+ }
+ }
+
+ private static void loadStoreSales(Connection conn, String s, Random rnd) throws SQLException {
+ String sql = "UPSERT INTO " + s + ".STORE_SALES (ss_item_sk, ss_ticket_number, ss_sold_date_sk,"
+ + " ss_sold_time_sk, ss_customer_sk, ss_cdemo_sk, ss_hdemo_sk, ss_addr_sk, ss_store_sk,"
+ + " ss_promo_sk, ss_quantity, ss_sales_price, ss_ext_sales_price, ss_ext_discount_amt,"
+ + " ss_list_price, ss_ext_list_price, ss_coupon_amt, ss_net_profit, ss_net_paid)"
+ + " VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
+ long ticket = 0;
+ try (PreparedStatement ps = conn.prepareStatement(sql)) {
+ for (int item = 1; item <= N_ITEMS; item++) {
+ // Each item sold on a handful of dates, giving broad coverage of (year, moy).
+ for (int dateSk = 1; dateSk <= N_DATES; dateSk += 2) {
+ ticket++;
+ int qty = 1 + rnd.nextInt(20);
+ double salesPrice = 5 + rnd.nextInt(95) + 0.5;
+ double listPrice = salesPrice + rnd.nextInt(20);
+ long cust = 1 + ((item + dateSk) % N_CUSTOMERS);
+ ps.setLong(1, item);
+ ps.setLong(2, ticket);
+ ps.setLong(3, dateSk);
+ ps.setLong(4, 1 + (ticket % N_TIMES));
+ ps.setLong(5, cust);
+ ps.setLong(6, 1 + ((cust - 1) % N_CDEMO));
+ ps.setLong(7, 1 + ((cust - 1) % N_HDEMO));
+ ps.setLong(8, 1 + ((cust - 1) % N_ADDR));
+ ps.setLong(9, 1 + (int) (ticket % N_STORES));
+ ps.setLong(10, 1 + (int) (ticket % N_PROMOS));
+ ps.setInt(11, qty);
+ ps.setBigDecimal(12, bd(salesPrice));
+ ps.setBigDecimal(13, bd(salesPrice * qty));
+ ps.setBigDecimal(14, bd(rnd.nextInt(50)));
+ ps.setBigDecimal(15, bd(listPrice));
+ ps.setBigDecimal(16, bd(listPrice * qty));
+ ps.setBigDecimal(17, bd(rnd.nextInt(20)));
+ ps.setBigDecimal(18, bd((salesPrice - 3) * qty));
+ ps.setBigDecimal(19, bd(salesPrice * qty));
+ ps.executeUpdate();
+ }
+ }
+ }
+ conn.commit();
+ }
+
+ private static void loadStoreReturns(Connection conn, String s, Random rnd) throws SQLException {
+ // Return roughly every 4th store_sales row, reusing its (item, ticket) so the natural join key
+ // lines up with store_sales.
+ String sql = "UPSERT INTO " + s + ".STORE_RETURNS (sr_item_sk, sr_ticket_number,"
+ + " sr_returned_date_sk, sr_customer_sk, sr_cdemo_sk, sr_hdemo_sk, sr_addr_sk, sr_store_sk,"
+ + " sr_reason_sk, sr_return_quantity, sr_return_amt, sr_net_loss, sr_fee)"
+ + " VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?)";
+ long ticket = 0;
+ try (PreparedStatement ps = conn.prepareStatement(sql)) {
+ for (int item = 1; item <= N_ITEMS; item++) {
+ for (int dateSk = 1; dateSk <= N_DATES; dateSk += 2) {
+ ticket++;
+ if (ticket % 2 != 0) {
+ continue;
+ }
Review Comment:
The comment says returns occur roughly every 4th `store_sales` row, but the code currently returns every 2nd row (`ticket % 2`). This makes the fixture documentation inaccurate and could confuse future changes that rely on the stated ratio.
##########
phoenix-core/src/it/java/org/apache/phoenix/end2end/tpcds/TPCDSLikeFixture.java:
##########
@@ -0,0 +1,914 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+package org.apache.phoenix.end2end.tpcds;
+
+import java.sql.Connection;
+import java.sql.PreparedStatement;
+import java.sql.ResultSet;
+import java.sql.SQLException;
+import java.sql.Statement;
+import java.util.Random;
+
+/**
+ * Shared deterministic TPC-DS derived fixture for {@link TPCDSLikeSingleChannelIT} and
+ * {@link TpcdsLikeCrossChannelIT}.
+ * <p>
+ * The schema is a trimmed subset of the TPC-DS v4.0.0 schema, only the tables and columns touched
+ * by the adapted queries, with the following uniform Phoenix adaptation rules applied:
+ * <ul>
+ * <li>Surrogate keys ({@code *_sk}) become single-column {@code BIGINT NOT NULL PRIMARY KEY}
+ * columns on the dimension tables.</li>
+ * <li>Sales and returns fact tables use a composite primary key on
+ * {@code (*_item_sk, *_ticket_number)} or {@code *_order_number}. {@code inventory} uses
+ * {@code (inv_date_sk, inv_item_sk, inv_warehouse_sk)}.</li>
+ * <li>Fact tables are salted ({@code SALT_BUCKETS=4}).</li>
+ * <li>Foreign key columns are plain nullable columns. Referential consistency is guaranteed by this
+ * loader, not by the schema.</li>
+ * </ul>
+ * <p>
+ * The data is generated with a fixed {@link Random} seed so that every adapted query has a stable
+ * reference answer.
+ * <p>
+ * The fixture is materialized into two schemas that hold identical data:
+ * <ul>
+ * <li>{@link #SCHEMA} ({@value #SCHEMA}) -- base tables, no secondary indexes.</li>
+ * <li>{@link #SCHEMA_INDEXED} ({@value #SCHEMA_INDEXED}) -- the same, plus a covering global index
+ * per fact table.</li>
+ * </ul>
+ */
+public final class TPCDSLikeFixture {
+
+ /** Base schema with no secondary indexes */
+ public static final String SCHEMA = "TPCDS";
+ /** Schema holding identical data plus a covering global index per fact table. */
+ public static final String SCHEMA_INDEXED = "TPCDSI";
+
+ private static final long SEED = 42L;
+
+ private static volatile boolean loaded = false;
+
+ private TPCDSLikeFixture() {
+ }
+
+ /** All base (dimension + fact) table short names, in dependency order, dimensions first. */
+ static final String[] TABLES =
+ { "DATE_DIM", "TIME_DIM", "ITEM", "CUSTOMER_ADDRESS", "CUSTOMER_DEMOGRAPHICS", "INCOME_BAND",
+ "HOUSEHOLD_DEMOGRAPHICS", "CUSTOMER", "STORE", "CALL_CENTER", "CATALOG_PAGE", "WEB_SITE",
+ "WEB_PAGE", "WAREHOUSE", "SHIP_MODE", "PROMOTION", "REASON", "STORE_SALES", "STORE_RETURNS",
+ "CATALOG_SALES", "CATALOG_RETURNS", "WEB_SALES", "WEB_RETURNS", "INVENTORY" };
+
+ /** Create both schemas and populate them with deterministic data. */
+ public static synchronized void load(Connection conn) throws SQLException {
+ if (loaded && tablesPopulated(conn)) {
+ return;
+ }
+ conn.setAutoCommit(false);
+ createTables(conn, SCHEMA);
+ createTables(conn, SCHEMA_INDEXED);
+ if (!tablesPopulated(conn)) {
+ loadData(conn, SCHEMA);
+ copyData(conn, SCHEMA, SCHEMA_INDEXED);
+ }
+ createIndexes(conn, SCHEMA_INDEXED);
+ conn.commit();
+ loaded = true;
+ }
+
+ private static boolean tablesPopulated(Connection conn) {
+ try (Statement st = conn.createStatement();
+ ResultSet rs = st.executeQuery("SELECT COUNT(*) FROM " + SCHEMA + ".STORE_SALES")) {
+ return rs.next() && rs.getInt(1) > 0;
+ } catch (SQLException e) {
+ return false;
+ }
+ }
+
Review Comment:
`tablesPopulated` only checks for data in the base schema (`TPCDS.STORE_SALES`). If the base schema is populated but the indexed schema (`TPCDSI`) is missing/empty (e.g., after a partial previous run or manual cleanup), `load()` will skip `copyData(...)`, leaving the indexed fixture empty and causing the parameterized `GLOBAL_INDEX` runs to fail. Consider validating population for both schemas.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: issues-unsubscribe@phoenix.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [PR] PHOENIX-7890 Improve EXPLAIN for joins and unions [phoenix]
Posted by "apurtell (via GitHub)" <gi...@apache.org>.
apurtell commented on PR #2510:
URL: https://github.com/apache/phoenix/pull/2510#issuecomment-4666850197
## Test Results
### Unit tests (connectionless)
| Test | Run | Failures | Errors | Skipped | Result |
| --- | ---: | ---: | ---: | ---: | :---: |
| `org.apache.phoenix.query.explain.ExplainPlanTest` | 63 | 0 | 0 | 0 | PASS |
| `org.apache.phoenix.compile.QueryCompilerTest` | 215 | 0 | 0 | 2 | PASS |
`ExplainPlanTest` includes the new `testUnionAllOfHashJoins` case covering the deeply nested
union-of-hash-joins explain output (union root → branch scan → hash-join sub-plan), plus the
updated `testHashJoinInner`, `testHashJoinSemiInSubquery`, `testSortMergeJoin`, and `testUnionAll`.
### Integration tests
| Test | Run | Failures | Errors | Skipped | Result |
| --- | ---: | ---: | ---: | ---: | :---: |
| `org.apache.phoenix.end2end.join.HashJoinNoIndexIT` | 33 | 0 | 0 | 0 | PASS |
| `org.apache.phoenix.end2end.join.HashJoinGlobalIndexIT` | 33 | 0 | 0 | 0 | PASS |
| `org.apache.phoenix.end2end.join.HashJoinLocalIndexIT` | 34 | 0 | 0 | 0 | PASS |
| `org.apache.phoenix.end2end.join.HashJoinMoreIT` | 10 | 0 | 0 | 0 | PASS |
| `org.apache.phoenix.end2end.index.IndexUsageIT` | 39 | 0 | 0 | 0 | PASS |
| `org.apache.phoenix.end2end.index.GlobalIndexOptimizationIT` | 7 | 0 | 0 | 0 | PASS |
| `org.apache.phoenix.end2end.QueryWithTableSampleIT` | 12 | 0 | 0 | 0 | PASS |
| `org.apache.phoenix.end2end.UnionAllIT` | 19 | 0 | 0 | 0 | PASS |
| `org.apache.phoenix.end2end.CostBasedDecisionIT` | 20 | 0 | 0 | 0 | PASS |
| `org.apache.phoenix.end2end.join.SortMergeJoinNoIndexIT` | 35 | 0 | 0 | 0 | PASS |
| `org.apache.phoenix.end2end.join.SortMergeJoinGlobalIndexIT` | 35 | 0 | 0 | 0 | PASS |
| `org.apache.phoenix.end2end.join.SortMergeJoinLocalIndexIT` | 35 | 0 | 0 | 0 | PASS |
| `org.apache.phoenix.end2end.SortMergeJoinMoreIT` | 10 | 0 | 0 | 0 | PASS |
| `org.apache.phoenix.end2end.join.SubqueryUsingSortMergeJoinIT` | 21 | 0 | 0 | 0 | PASS |
| `org.apache.phoenix.end2end.tpcds.TPCDSLikeSingleChannelIT` | 28 | 0 | 0 | 0 | PASS |
| `org.apache.phoenix.end2end.tpcds.TPCDSLikeCrossChannelIT` | 12 | 0 | 0 | 0 | PASS |
The two `TPCDSLike*IT` classes are new TPC-DS derived ITs that exercise joins and unions. Each query runs over a deterministic fixture and is parameterized across `NO_INDEX` and `GLOBAL_INDEX` schemas. Every case asserts the full ordered result plus the EXPLAIN operator markers (`HASH BUILD`, `UNION ALL OVER N QUERIES`, `SORT-MERGE-JOIN (INNER/FULL)`) in both parameters, and additionally verifies covering-index usage (`SS_I`, `INV_I`, `CS_I`) in the `GLOBAL_INDEX` parameter where the optimizer rewrites the fact-table scan onto a global index.
### Totals
- Unit tests: 278 run, 0 failures, 0 errors (2 skipped).
- Integration tests: 383 run, 0 failures, 0 errors, 0 skipped.
- **Overall: 661 run, 0 failures, 0 errors.**
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: issues-unsubscribe@phoenix.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org