基于hive数据库的泰坦尼克号幸存者数据分析

进入

./beeline -u jdbc:hive2://node2:10000 -n root -p

查询

SHOW TABLES;

删除

DROP TABLE IF EXISTS tidanic;

上传数据

hdfs dfs -put train.csv /user/hive/warehouse/mytrain.db/tidanic

《泰坦尼克号幸存者数据分析》

1、原始数据介绍

泰坦尼克号是当时世界上体积最庞大、内部设施最豪华的客运轮船，有“永不沉没”的美誉。然而不幸的是，在它的处女航中，泰坦尼克号便遭厄运——它从英国南安普敦出发驶向美国纽约。

（1）列名介绍

PassengerID->乘客ID
Survived->是否生还
Pclass->船舱级别
Name->姓名
Sex->性别
Age->年龄
SibSp->兄弟姐妹与配偶的总数
Parch->父母和孩子的总数
Ticket->船票ID
Fare->票价
Cabin->舱室
Embarked->出发港口

（2）经过数据清洗后字段之间分隔符为‘\t’, 集合之间分隔符为‘,’ 数据数目：891 条 创建原始表 tidanic

2、 创建数据库并进入数据库

create database if not exists mytrain;

use mytrain;
1
2
3

3、创建源表

create table tidanic(
passengerid int,
survived int,
pclass int,
name string,
sex string,
age int,
sibsp int,
parch int,
ticket string,
fare double,
cabin String,
embarked String)

row format delimited fields terminated by ',';
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

（1）通过HDFS命令导入数据到指定路径。

hdfs dfs -put train.csv   /user/hive/warehouse/mytrain.db/tidanic
1

（2）查看前5行，检查是否导入成功。

select * from tidanic limit 5;
1

4、静态分区表

（1）创建静态分区表tidanic_part,字段为passengerid,survived,pclass,name,

分区字段为gender，按照性别字段sex分区。

  create table tidanic_part(
  passengerid int,
  survived int,
  pclass int,
  name string)
  partitioned by(gender string)
  row format delimited fields terminated by ',';
1
2
3
4
5
6
7

（2）导入数据到静态分区表tidanic_part

  insert overwrite table tidanic_part partition(gender='female')
  select passengerid,survived,pclass,name from tidanic where sex='female';

  insert overwrite table tidanic_part partition(gender='male')
  select passengerid,survived,pclass,name from tidanic where sex='male';
1
2
3
4
5

5、动态分区表

（1）创建动态分区表tidanic_dynamic_part，字段为passengerid,survived,name,

分区字段为passengerclass,按照pclass值进行分区。

  create table tidanic_dynamic_part(
   passengerid int,
   survived int,
   name string)
   partitioned by(passengerclass string)
   row format delimited fields terminated by ',';
1
2
3
4
5
6

（2）设置动态分区配置

   set  hive.exec.dynamic.partition=true;
   set  hive.exec.dynamic.partition.mode=nostrict;
1
2

（3）往动态分区表中插入数据

  insert overwrite table tidanic_dynamic_part partition(passengerclass)
  select passengerid,survived,name,pclass from tidanic;
1
2

6、分桶表

（1）创建桶表，按年龄将数据分到4个桶，抽取两个桶的数据创建一个新表tidannic_sample。

  create table tidanic_bucket(
  passengerid int,
  name string,
  age int)
  clustered by (age) into 4 buckets
  row format delimited fields terminated by ',';
1
2
3
4
5
6

（2）修改桶表配置

set hive.enforce.bucketing=true;
1

（3）往桶表中插入数据

   insert overwrite table tidanic_bucket 
   select passengerid,name,age from tidanic;
1
2

（4）抽取桶1开始两个桶的数据到抽样表tidanic_sample中,

   create table tidanic_sample as 
   select * from tidanic_bucket tablesample(bucket 1 out of 2 on age);
1
2

7、数据导出

将分区标数据导出到文件夹‘/export_dir2’

export table tidanic_dynamic_part to '/user/hive/export_dir2';
1

8、外部表

（1）创建外部表，位置位于’/user/hive/warehouse/titanic_external’，字符之间’,'隔开

CREATE EXTERNAL TABLE titanic_external (
passengerid int,
survived int,
pclass int,
name string,
sex string,
age int,
sibsp int,
parch int,
ticket string,
fare double,
cabin String,
embarked String
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
LOCATION '/user/hive/warehouse/titanic_external';
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

（2）载入数据

LOAD DATA INPATH '/train.csv' INTO TABLE titanic_external;
1

（3）查看表的结构

DESCRIBE FORMATTED titanic_external;
1

9、DDL操作

①显示表名t的数据表

②将数据库中titanic_external表的名字改为titanic_ex;

③删除数据表titanic_ex；

DROP TABLE titanic_ex;
1

10、查询插入

所有年龄大于等于 20 岁的乘客数据插入到另一个表 titanic_cc中

INSERT INTO TABLE titanic_cc
SELECT *
FROM titanic_external
WHERE Age >= 20;
1
2
3
4

11、分组过滤排序查询

（1）过滤查询（WHERE）：查询所有幸存下来的男性乘客。

SELECT *   FROM tidanic   WHERE sex = 'male' AND survived = 1;
1

（2）分组查询（GROUP BY）：按船票等级（pclass）统计乘客数。

SELECT pclass, COUNT(*) AS num_passengers   FROM tidanic   GROUP BY pclass;
1

（3）排序查询（ORDER BY）：按船票费用（fare）从高到低排序乘客。

SELECT *   FROM tidanic   ORDER BY fare DESC;
1

（4）组合过滤、分组和排序：查询所有幸存下来的女性乘客，并按年龄（age）从低到高排序。

SELECT *   FROM tidanic   WHERE sex = 'female' AND survived = 1   ORDER BY age ASC;
1

（5）内置函数 - 数学函数：计算乘客年龄的标准差。

SELECT STDDEV(Age) AS age_stddev
FROM tidanic;
1
2

（6）内置函数 - 条件函数： 使用CASE语句将乘客分为成年人和未成年人，并计算各自的数量。

SELECT
    SUM(CASE WHEN Age >= 18 THEN 1 ELSE 0 END) AS adult_count,
    SUM(CASE WHEN Age < 18 THEN 1 ELSE 0 END) AS minor_count
FROM tidanic;
1
2
3
4

12、抽样查询

从tidanic中随机选择大约10%的行

SELECT *  

FROM tidanic  

TABLESAMPLE(BUCKET 1 OUT OF 10 ON RAND()) s;
1
2
3
4
5

13、事务表

开启事务

set hive.support.concurrency = true; 
set hive.enforce.bucketing = true;
set hive.exec.dynamic.partition.mode = nonstrict; 
set hive.txn.manager = org.apache.hadoop.hive.ql.lockmgr.DbTxnManager; 
set hive.compactor.initiator.on = true; 
set hive.compactor.worker.threads = 1; 
1
2
3
4
5
6

创建表

CREATE TABLE titanic_transactional (  

passengerid int,
survived int,
pclass int,
name string,
sex string,
age int,
sibsp int,
parch int,
ticket string,
fare double,
cabin String,
embarked String

)  

STORED AS ORC  

TBLPROPERTIES ('transactional'='true');
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

从原表把数据插入事务表

INSERT INTO TABLE titanic_transactional  

SELECT * FROM  tidanic;
1
2
3

更新所有年龄大于60的乘客的survived字段为0（表示未幸存）

UPDATE titanic_transactional   SET survived = 0   WHERE age > 60;
1

SELECT * FROM titanic_transactional WHERE age < 20;
1

删除所有年龄小于20的乘客记录

DELETE FROM titanic_transactional WHERE age < 20;
1

SELECT * FROM titanic_transactional WHERE age < 20;
1

分析与总结：

使用python把筛选出的数据进行数据分析可得到如下

• 女性幸存率约为75%，远高于男性的20%左右。这表明在紧急情况下，女性更容易得到救援。
• 头等舱乘客的幸存率最高，达到了63%，而三等舱乘客的幸存率最低，仅为24%。这表明社会地位和经济条件对幸存率有显著影响。
• 在各个船舱等级中，女性的幸存率均高于男性。然而，头等舱男性的幸存率仍然高于三等舱女性的幸存率，这进一步强调了社会地位对幸存率的重要性。
• 与家人同行的乘客往往更容易幸存，因为他们可以相互帮助和照顾。