Skip to main content

Impala usage samples

[root@hadoop-yarn1 hbase]# su hdfs


tab1.csv:
1,true,123.123,2012-10-24 08:55:00
2,false,1243.5,2012-10-25 13:40:00
3,false,24453.325,2008-08-22 09:33:21.123
4,false,243423.325,2007-05-12 22:32:21.33454
5,true,243.325,1953-04-22 09:11:33

tab2.csv:

1,true,12789.123
2,false,1243.5
3,false,24453.325
4,false,2423.3254
5,true,243.325
60,false,243565423.325
70,true,243.325
80,false,243423.325
90,true,243.325

Put each .csv file into a separate HDFS directory using commands like the following, which use paths available in the Impala Demo VM:

$ hdfs dfs -mkdir /user/ercan/tab1
$ hdfs dfs -put tab1.csv /user/ercan/tab1
$ hdfs dfs -cat /user/ercan/tab1/tab1.csv


$ hdfs dfs -ls /user/ercan/tab1
Found 1 items
-rw-r--r--   1 ercan ercan        192 2013-04-02 20:08 /user/ercan/tab1/tab1.csv

$ hdfs dfs -mkdir /user/ercan/tab2
$ hdfs dfs -put tab2.csv /user/ercan/tab2
$ hdfs dfs -ls /user/ercan/tab2



DROP TABLE IF EXISTS tab1;
CREATE EXTERNAL TABLE tab1
(
   id INT,
   col_1 BOOLEAN,
   col_2 DOUBLE,
   col_3 TIMESTAMP
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
LOCATION '/user/ercan/tab1';

DROP TABLE IF EXISTS tab2;
CREATE EXTERNAL TABLE tab2
(
   id INT,
   col_1 BOOLEAN,
   col_2 DOUBLE
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
LOCATION '/user/ercan/tab2';

DROP TABLE IF EXISTS tab3;
CREATE TABLE tab3
(
   id INT,
   col_1 BOOLEAN,
   col_2 DOUBLE,
   month INT,
   day INT
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';



$ impala-shell -i hadoop-yarn1.mydomain.com -f q.sql

$ impala-shell
[Not connected] > connect hadoop-yarn1.mydomain.com;


[hadoop-yarn1.mydomain.com:21000] > select * from tab1;
Query: select * from tab1
Query finished, fetching results ...
+----+-------+------------+-------------------------------+
| id | col_1 | col_2      | col_3                         |
+----+-------+------------+-------------------------------+
| 1  | true  | 123.123    | 2012-10-24 08:55:00           |
| 2  | false | 1243.5     | 2012-10-25 13:40:00           |
| 3  | false | 24453.325  | 2008-08-22 09:33:21.123000000 |
| 4  | false | 243423.325 | 2007-05-12 22:32:21.334540000 |
| 5  | true  | 243.325    | 1953-04-22 09:11:33           |
+----+-------+------------+-------------------------------+
Returned 5 row(s) in 28.36s
[hadoop-yarn1.mydomain.com:21000] >


[hadoop-yarn1.mydomain.com:21000] > select * from tab1 t join tab2 a on t.col_1=a.col_1;
Query: select * from tab1 t join tab2 a on t.col_1=a.col_1
Query finished, fetching results ...
+----+-------+------------+-------------------------------+----+-------+---------------+
| id | col_1 | col_2      | col_3                         | id | col_1 | col_2         |
+----+-------+------------+-------------------------------+----+-------+---------------+
| 1  | true  | 123.123    | 2012-10-24 08:55:00           | 90 | true  | 243.325       |
| 1  | true  | 123.123    | 2012-10-24 08:55:00           | 70 | true  | 243.325       |
| 1  | true  | 123.123    | 2012-10-24 08:55:00           | 5  | true  | 243.325       |
| 1  | true  | 123.123    | 2012-10-24 08:55:00           | 1  | true  | 12789.123     |
| 2  | false | 1243.5     | 2012-10-25 13:40:00           | 80 | false | 243423.325    |
| 2  | false | 1243.5     | 2012-10-25 13:40:00           | 60 | false | 243565423.325 |
| 2  | false | 1243.5     | 2012-10-25 13:40:00           | 4  | false | 2423.3254     |
| 2  | false | 1243.5     | 2012-10-25 13:40:00           | 3  | false | 24453.325     |
| 2  | false | 1243.5     | 2012-10-25 13:40:00           | 2  | false | 1243.5        |
| 3  | false | 24453.325  | 2008-08-22 09:33:21.123000000 | 80 | false | 243423.325    |
| 3  | false | 24453.325  | 2008-08-22 09:33:21.123000000 | 60 | false | 243565423.325 |
| 3  | false | 24453.325  | 2008-08-22 09:33:21.123000000 | 4  | false | 2423.3254     |
| 3  | false | 24453.325  | 2008-08-22 09:33:21.123000000 | 3  | false | 24453.325     |
| 3  | false | 24453.325  | 2008-08-22 09:33:21.123000000 | 2  | false | 1243.5        |
| 4  | false | 243423.325 | 2007-05-12 22:32:21.334540000 | 80 | false | 243423.325    |
| 4  | false | 243423.325 | 2007-05-12 22:32:21.334540000 | 60 | false | 243565423.325 |
| 4  | false | 243423.325 | 2007-05-12 22:32:21.334540000 | 4  | false | 2423.3254     |
| 4  | false | 243423.325 | 2007-05-12 22:32:21.334540000 | 3  | false | 24453.325     |
| 4  | false | 243423.325 | 2007-05-12 22:32:21.334540000 | 2  | false | 1243.5        |
| 5  | true  | 243.325    | 1953-04-22 09:11:33           | 90 | true  | 243.325       |
| 5  | true  | 243.325    | 1953-04-22 09:11:33           | 70 | true  | 243.325       |
| 5  | true  | 243.325    | 1953-04-22 09:11:33           | 5  | true  | 243.325       |
| 5  | true  | 243.325    | 1953-04-22 09:11:33           | 1  | true  | 12789.123     |
+----+-------+------------+-------------------------------+----+-------+---------------+
Returned 23 row(s) in 0.60s


[hadoop-yarn1.mydomain.com:21000] > select * from tab1 t join tab2 a on t.col_1=a.col_1 where a.id >80;
Query: select * from tab1 t join tab2 a on t.col_1=a.col_1 where a.id >80
Query finished, fetching results ...
+----+-------+---------+---------------------+----+-------+---------+
| id | col_1 | col_2   | col_3               | id | col_1 | col_2   |
+----+-------+---------+---------------------+----+-------+---------+
| 1  | true  | 123.123 | 2012-10-24 08:55:00 | 90 | true  | 243.325 |
| 5  | true  | 243.325 | 1953-04-22 09:11:33 | 90 | true  | 243.325 |
+----+-------+---------+---------------------+----+-------+---------+
Returned 2 row(s) in 0.57s




[hadoop-yarn1.mydomain.com:21000] > explain  select * from tab1 t join tab2 a on t.col_1=a.col_1;
Explain query: select * from tab1 t join tab2 a on t.col_1=a.col_1
PLAN FRAGMENT 0
  PARTITION: UNPARTITIONED

  4:EXCHANGE
     tuple ids: 0 1

PLAN FRAGMENT 1
  PARTITION: RANDOM

  STREAM DATA SINK
    EXCHANGE ID: 4
    UNPARTITIONED

  2:HASH JOIN
  |  join op: INNER JOIN (BROADCAST)
  |  hash predicates:
  |    t.col_1 = a.col_1
  |  tuple ids: 0 1
  | 
  |----3:EXCHANGE
  |       tuple ids: 1
  |   
  0:SCAN HDFS
     table=default.tab1 #partitions=1 size=193B
     tuple ids: 0

PLAN FRAGMENT 2
  PARTITION: RANDOM

  STREAM DATA SINK
    EXCHANGE ID: 3
    UNPARTITIONED

  1:SCAN HDFS
     table=default.tab2 #partitions=1 size=158B compact
     tuple ids: 1

[hadoop-yarn1.mydomain.com:21000] >



[hadoop-yarn1.mydomain.com:21000] > show tables;
Query: show tables
Query finished, fetching results ...
+------+
| name |
+------+
| tab1 |
| tab2 |
| tab3 |
+------+




create 'hbasealltypessmall', 'bools', 'ints', 'floats', 'strings'


Comments

Popular posts from this blog

Offline AI Coding in VSCode: Continue + Ollama Complete Setup Guide

Want GitHub Copilot-level code assistance — but fully offline, air-gapped, and private? This guide walks you through setting up Continue (a VSCode extension) with Ollama (a local model runner) so you get chat, autocomplete, and inline editing powered entirely by models running on your own machine. No internet required at runtime. Table of Contents What Are Continue and Ollama? Prerequisites & Hardware Requirements Choosing the Right Models Step 1 — Installing Ollama & Pulling Models Step 2 — Installing Continue in VSCode (Offline) Step 3 — Configuring config.yaml Step 4 — Disabling Telemetry Step 5 — Usage & Shortcuts Troubleshooting What Are Continue and Ollama? Continue is an open-source VSCode (and JetBrains) extension that adds AI-powered coding features directly into your editor — think chat, inline edits, and tab autocomplete. By default it can connect to Claude, GPT-4, or any API. But it also supports local providers , which is exactly what we want here. Ollama i...

Local LLM on Ubuntu (CPU-Only): Ollama + Qwen Coder + Open WebUI + RAG Setup

Local LLM on Ubuntu (CPU-Only): Ollama + Qwen Coder + Open WebUI + RAG Run a private, high-performance internal AI coding assistant on your own hardware—no GPU required. Feb 13, 2026 Qwen 2.5 Coder RAG Enabled Enterprise Ready Keeping your code and documents on your own infrastructure is the gold standard for enterprise security. By combining Ollama , Qwen Coder , and Open WebUI , we can build a production-grade AI assistant that respects your privacy. Environment: CPU-only Ubuntu server (e.g., i9/Threadripper) with 64GB+ RAM for smooth 14B model inference. 1. System Preparation First, update your local environment and install necessary dependencies: sudo apt update && sudo apt upgrade -y sudo apt install -y curl git ca-certificates lscpu 2. Ollama & Model Tuning Install Ollama and optimize the configuration for CPU-only inference by setting the thread count: ...

Defining tomcat and mysql server Amazon EC2 instance

-- Create new amazon ec2 instance http://docs.aws.amazon.com/AWSEC2/latest/UserGuide/EC2_GetStarted.html -- Give permisson to pem file chmod 400 mycertificate.pem -- Connect to EC2 instance ssh -i mycertificate.pem ubuntu@ec2-51-51-51-51.eu-west-1.compute.amazonaws.com -- Installation Oracle Java 1.8 $ wget --no-check-certificate --no-cookies --header "Cookie: oraclelicense=accept-securebackup-cookie" http://download.oracle.com/otn-pub/java/jdk/8u92-b14/jdk-8u92-linux-x64.tar.gz $ tar -xzvf jdk-8u92-linux-x64.tar.gz $ sudo mkdir -p /usr/java/ $ sudo mv jdk1.8.0_92/ /usr/java/ $ sudo vim  /etc/profile         JAVA_HOME=/usr/java/jdk1.8.0_92         PATH=$PATH:$HOME/bin:$JAVA_HOME/bin         JRE_HOME=/usr/java/jdk1.8.0_92/jre         PATH=$PATH:$HOME/bin:$JRE_HOME/bin         export JAVA_HOME...