Shulou(Shulou.com)06/03 Report--

Write the HQL statement of Hive to achieve the following results: 1 in the table indicates optional, and 0 in the table indicates that it is not optional.

Id a b c d e f

1 1 1 0 1 0

2 1 0 1 1 0 1

3 1 1 1 0 1 0

It indicates that some students whose id is 1pr 2je 3 have taken a few of the classes in the course aPercience brecrium crec rep e pr.

Id course

1,a

1,b

1,c

1,e

2,a

2,c

2,d

2,f

3,a

3,b

3,c

3,e

Create table t_stu_course

(

Id int

Course string

) row format delimited fields terminated by ","

Load data local inpath "/ root/t_stu_course.txt" into table t_stu_course

Select tmp.id

, max (tmp.a) as a

, max (tmp.b) as b

, max (tmp.c) as c

, max (tmp.d) as d

, max (tmp.e) as e

, max (tmp.f) as f

From (

Select id

, case when course= "a" then 1 else 0 end as a

, case when course= "b" then 1 else 0 end as b

, case when course= "c" then 1 else 0 end as c

, case when course= "d" then 1 else 0 end as d

, case when course= "e" then 1 else 0 end ase

, case when course= "f" then 1 else 0 end as f

From t_stu_course

) tmp

Group by tmp.id

Select collect_set (course) as courses from t_stu_course

Set hive.strict.checks.cartesian.product=false

Select t1.id as id,t1.course as id_courses,t2.course courses

From

(select id as id,collect_set (course) as course from t_stu_course group by id) T1

Join

(select collect_set (course) as course from t_stu_course) T2

Enable strict mode: hive.mapred.mode = strict / / Deprecated

Hive.strict.checks.large.query = true

This setting disables: 1. Do not specify an orderby for paging

two。 Query the partition table without specifying a partition

3. It has nothing to do with the amount of data, it's just a query mode.

Hive.strict.checks.type.safety = true

Strict type safety, this property does not allow the following operations: 1. Comparison between bigint and string

2. Comparison between bigint and double

Hive.strict.checks.cartesian.product = true

This property does not allow Cartesian product operations.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.