Object Storage Service:Query OSS data using Apache Impala (CDH6)

Verify the OSS connection

Run these commands to confirm that Hadoop can read from and write to OSS:

# List objects in your bucket
hadoop fs -ls oss://${your-bucket-name}/

# Create a test directory
hadoop fs -mkdir oss://${your-bucket-name}/hadoop-test

If both commands succeed, the configuration is correct. If either fails, check the parameter values — especially fs.oss.endpoint and the AccessKey credentials.

All content in ${} is environment variables. Modify these environment variables.

Create symbolic links

Navigate to the ${CDH_HOME}/lib/impala directory and create symbolic links to the OSS JAR files:

cd lib/
ln -s ../../../jars/hadoop-aliyun-3.0.0-cdh6.0.1.jar hadoop-aliyun.jar
ln -s ../../../jars/aliyun-sdk-oss-2.8.3.jar aliyun-sdk-oss-2.8.3.jar
ln -s ../../../jars/jdom-1.1.jar jdom-1.1.jar

Update CLASSPATH in Impala startup files

Navigate to the ${CDH_HOME}/bin directory. In each of the three files — impalad, statestored, and catalogd — add the following line immediately before the final exec command:

export CLASSPATH=${CLASSPATH}:${IMPALA_HOME}/lib/hadoop-aliyun.jar:${IMPALA_HOME}/lib/aliyun-sdk-oss-2.8.3.jar:${IMPALA_HOME}/lib/jdom-1.1.jar

After updating all three files, restart the Impala-related processes on all nodes.

Generate sample data

Clone the hive-testbench repository and generate a TPC-DS dataset (scale factor 50) in OSS:

git clone https://github.com/hortonworks/hive-testbench.git
cd hive-testbench
git checkout hive14
./tpcds-build.sh
FORMAT=textfile ./tpcds-setup.sh 50 oss://{your-bucket-name}/

Create external tables

Use the DDL statements in ddl-tpcds/text/alltables.sql to create tables pointing to the OSS data. Replace ${LOCATION} in each statement with the corresponding OSS path.

The following example creates the call_center external table:

CREATE EXTERNAL TABLE call_center(
      cc_call_center_sk         bigint
,     cc_call_center_id         string
,     cc_rec_start_date         string
,     cc_rec_end_date           string
,     cc_closed_date_sk         bigint
,     cc_open_date_sk           bigint
,     cc_name                   string
,     cc_class                  string
,     cc_employees              int
,     cc_sq_ft                  int
,     cc_hours                  string
,     cc_manager                string
,     cc_mkt_id                 int
,     cc_mkt_class              string
,     cc_mkt_desc               string
,     cc_market_manager         string
,     cc_division               int
,     cc_division_name          string
,     cc_company                int
,     cc_company_name           string
,     cc_street_number          string
,     cc_street_name            string
,     cc_street_type            string
,     cc_suite_number           string
,     cc_city                   string
,     cc_county                 string
,     cc_state                  string
,     cc_zip                    string
,     cc_country                string
,     cc_gmt_offset             double
,     cc_tax_percentage         double
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '|'
LOCATION 'oss://{your-bucket-name}/50/call_center';

Verify that all 24 tables were created:

SHOW TABLES;

Run a TPC-DS query

Connect to the Impala shell and run one of the provided SQL files:

impala-shell -i cdh-slave01 -d default -f sample-queries-tpcds/query13.sql

The query in query13.sql joins six tables — store, store_sales, customer_demographics, household_demographics, customer_address, and date_dim — and returns aggregate sales metrics. A successful run produces output similar to:

+-------------------+-------------------------+----------------------------+----------------------------+
| avg(ss_quantity)  | avg(ss_ext_sales_price) | avg(ss_ext_wholesale_cost) | sum(ss_ext_wholesale_cost) |
+-------------------+-------------------------+----------------------------+----------------------------+
| 30.87106918238994 | 2352.642327044025       | 2162.600911949685          | 687707.09                  |
+-------------------+-------------------------+----------------------------+----------------------------+
Fetched 1 row(s) in 353.16s

The sample-queries-tpcds/ directory contains additional SQL files (query3.sql through query98.sql) for broader performance testing.