· 8 years ago · Feb 08, 2018, 08:56 AM
1Using Apache Phoenix on labs:
2
3export PHOENIX_HOME=/usr/hdp/2.5.0.0-1245/phoenix/
4export PATH=$PATH:$PHOENIX_HOME/bin
5
6
7# Save the files us_population.sql, us_populatipn.csv and us_population_queries.sql with the below information
8
9us_population.sql
10
11CREATE TABLE IF NOT EXISTS us_population (
12 state CHAR(2) NOT NULL,
13 city VARCHAR NOT NULL,
14 population BIGINT
15 CONSTRAINT my_pk PRIMARY KEY (state, city));
16
17
18us_population.csv
19
20NY,New York,8143197
21CA,Los Angeles,3844829
22IL,Chicago,2842518
23TX,Houston,2016582
24PA,Philadelphia,1463281
25AZ,Phoenix,1461575
26TX,San Antonio,1256509
27CA,San Diego,1255540
28TX,Dallas,1213825
29CA,San Jose,912332
30
31
32
33us_population_queries.sql
34
35
36SELECT state as "State",count(city) as "City Count",sum(population) as "Population Sum"
37FROM us_population
38GROUP BY state
39ORDER BY sum(population) DESC;
40
41
42export PHOENIX_HOME=/usr/hdp/2.5.0.0-1245/phoenix/
43export PATH=$PATH:$PHOENIX_HOME/bin
44
45./psql.py nn01.itversity.com:2181 us_population.sql us_population.csv us_population_queries.sql