Hive基础

Hive是由Google实现并且开源的一个基于Hadoop的一个数据仓库工具，其可以将结构化的数据映射为一张数据库表，并且提供HQL（Hive SQL）查询功能。其底层数据存储在HDFS上，HDFS（Hadoop Distributed File System）是GFS（Google File System）的实现，是Hadoop项目的核心子项目，是分布式计算中数据存储管理的基础。Hive具有目前Hadoop上最丰富最全的SQL语法，也拥有最稳定的执行（但同时也最慢）。是目前Hadoop上几乎标准的ETL和数据仓库工具。

Hive的本质是将SQL语句转换为MapReduce任务运行，使得不熟悉mapReduce的用户很方便的利用HQL处理和计算HDFS上的结构化的数据，适用于离线的批量数据计算。实质就是一款基于HDFS的MapReduce计算框架，对于存储在HDFS中的数据进行分析和管理。

数据仓库之父比尔·恩门（Bill Inmon）在 1991 年出版的“Building the Data Warehouse”（《建 立数据仓库》）一书中所提出的定义被广泛接受——数据仓库（Data Warehouse）是一个面 向主题的（Subject Oriented）、集成的（Integrated）、相对稳定的（Non-Volatile）、反映历史 变化（Time Variant）的数据集合，用于支持管理决策(Decision Making Support)。

1
2

SELECT * FROM A WHERE id IN (SELECT id FROM B);
SELECT * FROM A WHERE exists (SELECT 1 FROM B WHERE A.id=B.id);

对于以上两种情况，in是在内存里遍历比较，而exists需要查询数据库，所以当B表数据量较大时，exists效率优于in。

IN语句工作原理

IN()只执行一次，其查出B表中的所有id字段并缓存起来。之后，检查A表的id是否与B表中的id相等，如果相等则将A表的记录加入结果集中，直到遍历完A表的所有记录。
它的查询过程类似于以下过程：

1
2
3
4
5
6
7
8
9
10
11
12
13

List resultSet={};
Array A=(select * from A);
Array B=(select id from B);

for(int i=0;i<A.length;i++) {
  for(int j=0;j<B.length;j++) {
      if(A[i].id==B[j].id) {
        resultSet.add(A[i]);
        break;
      }
  }
}
return resultSet;

可以看出，当B表数据较大时不适合使用in()，因为它会B表数据全部遍历一次

• 例1：A表有10000条记录，B表有1000000条记录，那么最多有可能遍历10000*1000000次，效率很差。
• 例2：A表有10000条记录，B表有100条记录，那么最多有可能遍历10000*100次，遍历次数大大减少，效率大大提升。

结论：IN()适合B表比A表数据小的情况

1. 对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。

2. 应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：

   1	SELECT id FROM t WHERE num IS NULL

   可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：

   1	SELECT if FROM t WHERE num=0

3. 应尽量避免在 where 子句中使用!=或<>操作符，否则引擎将放弃使用索引而进行全表扫描。

4. 应尽量避免在 where 子句中使用or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如：

   1	SELECT id FROM t WHERE num=10 OR num=20

   可以这样查询：

   1 2 3

   SELECT id FROM t WHERE num=10 UNION ALL SELECT id FROM t WHERE num=20

5. in 和 not in 也要慎用，否则会导致全表扫描，如：select id from t where num in(1,2,3) 对于连续的数值，能用 between 就不要用 in 了：

   1	SELECT id FROM t WHERE num BETWEEN 1 and 3

6. 下面的查询也将导致全表扫描：

   1	SELECT id FROM t WHERE name LIKE '李%'

   若要提高效率可以考虑全文检索。

   阅读全文 »

SQL COUNT查询优化

查询记录表总量有两种方法

1
2

SELECT COUNT(*) FROM table;
SELECT COUNT(col_name) FROM table;

这两种方法效果有所不同，COUNT(*)返回的一定是表记录总量，COUNT(col_name)返回的是该字段不为NULL值得记录总量。这两种查询可能一直也可能不一致。

在表记录较多的情况下，COUNT(*)的执行时间会比较长。在生产环境这非常可怕。因为这一慢查询的执行，可能引起其他SQL的效率低下，有可能引起链式反应像核爆炸那样耗尽服务器资源。

我们尽量要在生产环境避免这类语句执行。如果一定要，那么在满足一定条件下可以使用下面的小技巧来优化。

卡方分布（$\chi^2$分布）

卡方分布（chi-square distribution, χ²-distribution，或写作χ²分布）是概率论与统计学中常用的一种概率分布。k个独立的标准正态分布变量的平方和服从自由度为k的卡方分布。卡方分布是一种特殊的伽玛分布，是统计推断中应用最为广泛的概率分布之一，例如假设检验和置信区间的计算。

通常我们用它来检验理论分布与观测吻合得有多好。（皮尔森卡方检定的一种应用）

由卡方分布延伸出来皮尔森卡方检定常用于：

1. 样本某性质的比例分布与总体理论分布的拟合优度（例如某行政机关男女比是否符合该机关所在城镇的男女比）
2. 同一总体的两个随机变量是否独立（例如人的身高与交通违规的关联性）
3. 二或多个总体同一属性的同素性检定（意大利面店和寿司店的营业额有没有差距）

线性回归-平方误差

在统计学中，线性回归（英语：linear regression）是利用称为线性回归方程的最小二乘函数对一个或多个自变量和因变量之间关系进行建模的一种回归分析。这种函数是一个或多个称为回归系数的模型参数的线性组合。只有一个自变量的情况称为简单回归，大于一个自变量情况的叫做多元回归（multivariate linear regression）。

在这种回归算法中如果使用线性函数$y = mx + b$来建模，并且未知的模型参数也是通过数据来估计，那么这种模型被叫做线性模型。

样本均值之差的假设检验

假设X和Y是两个完全独立的随机变量，假设我们已知：
$$
E(X) = \mu_X \quad E(Y) = \mu_Y \\
Var(X) = E((X - \mu_X)^2) = \sigma_X^2 \quad Var(Y) = E((Y - \mu_Y)^2) = \sigma_Y^2
$$
现在我们对这两个随机变量进行采样，得到采样均值$\bar{X}$和$ \bar{Y}$与采样方差：
$$
\sigma^2_{\bar{X}} = \frac{\sigma^2_{X}}{n} \qquad \sigma^2_{\bar{Y}} = \frac{\sigma^2_{Y}}{m}
$$
现在我们设新的变量Z为以上两个采样均值的差：
$$
Z = \bar{X} - \bar{Y}
$$
对于这个新的变量的分布，我们可以知道：
$$
\mu_{\bar{X} - \bar{Y}} = \mu_{\bar{X}} - \mu_{\bar{Y}} \\
\sigma^2_{\bar{X} - \bar{Y}} = \sigma^2_{\bar{X}} - \sigma^2_{\bar{Y}}
$$
再展开我们有：
$$
\sigma^2_{\bar{X} - \bar{Y}} = \frac{\sigma^2_X}{n} + \frac{\sigma^2_Y}{m} \\
\sigma_{\bar{X} - \bar{Y}} = \sqrt{\frac{\sigma^2_X}{n} + \frac{\sigma^2_Y}{m} }
$$

假设检验

统计上对参数的假设，就是对一个或多个参数的论述。而其中欲检验其正确性的为零假设（null hypothesis），通常表示为$H_0$，零假设通常由研究者决定，反映研究者对未知参数的看法。相对于零假设的其他有关参数之论述是备择假设（alternative hypothesis），通常表示为$H_1$，它通常反应了执行检定的研究者对参数可能数值的另一种（对立的）看法，当经过假设检验后，如果零假设被否定，那么我们转而接受备择假设。

假设检验的种类包括：Student-t检验，Z检验，卡方检验，F检验等等。

中心极限定理（Central Limit Theorem）

中心极限定理说明，在适当的条件下，大量相互独立随机变量的均值（或者和）经适当标准化后依分布收敛于正态分布。这组定理是数理统计学和误差分析的理论基础，指出了大量随机变量之和近似服从正态分布的条件。

样本均值的抽样分布（Sampling Distribution of the sample mean）

这个分布趋于正态分布，其分布的数学期望为总体均值$\mu$，方差为总体方差的$\frac{1}{n}$（n为样本量）。那么自然的，标准差为总体标准差的$\frac{1}{\sqrt{n}}$。

置信区间（Confidence interval）

一个概率样本的置信区间，是对产生这个样本对的总体的参数分布（parametric distribution）中的摸一个未知参数值，以区间的形式给出的估计。

例如：正态分布，已知总体方差为$\sigma^2$，那么1-$\alpha$水平的正态置信区间为：
$$
\left(\overline{x}-z_{1-\alpha / 2} \frac{\sigma}{\sqrt{n}}, \overline{x}+z_{1-\alpha / 2} \frac{\sigma}{\sqrt{n}}\right) (双边) \\
\left(-\infty, \overline{x}+z_{1-\alpha} \frac{\sigma}{\sqrt{n}}\right) (单边) \\
\left(\overline{x}-z_{1-\alpha} \frac{\sigma}{\sqrt{n}},+\infty\right) （单边）
$$

大数定律（law of large number)

根据这个定律知道，样本数量越多，则其算术平均值就有越高的概率接近期望值。

样本均值
$$
\overline{X}_{n}=\frac{1}{n}\left(X_{1}+\cdots+X_{n}\right)
$$
收敛于真值
$$
\overline{X}_{n} \rightarrow \mu \quad \text { as } \quad n \rightarrow \infty
$$

正态分布（norm distribution）

正态分布（normal distribution）又名高斯分布（Gaussian distribution），是一个非常常见的连续概率分布。

若随机变量X服从一个位置参数为$\mu$，尺度参数为$\sigma​$的正态分布，记为：
$$
X \sim N\left(\mu, \sigma^{2}\right)
$$
正态分布的概率密度函数为：
$$
f(x)=\frac{1}{\sigma \sqrt{2 \pi}} exp\left({-\frac{(x-\mu)^{2}}{2 \sigma^{2}}}\right)
$$
正态分布的数学期望值或期望值$\mu$等于位置参数，决定了分布的位置；其方差$\sigma^2$的开平方或标准差$\sigma$等于尺度参数，决定了分布的幅度。我们常说的标准正态分布是$\mu = 0$并且$\sigma^2 =1 $的正态分布。