search
HomeDatabaseMysql Tutorial[置顶] 数据仓库----Hive进阶篇 一

数据仓库—-hive进阶篇二(表的链接,子查询,客户端jdbc和Thrift Client操作,自定义函数) 一、数据的导入 1、使用Load语句执行数据的导入 1.语法: 其中(中括号中表示可加指令): LOCAL:表示指定的文件路径是否是本地的,没有则说明是HDFS上的文件路径

数据仓库—-hive进阶篇二(表的链接,子查询,客户端jdbc和Thrift Client操作,自定义函数)

一、数据的导入

1、使用Load语句执行数据的导入

<code>1.语法:
</code>

这里写图片描述

<code>    其中(中括号中表示可加指令):
        LOCAL:表示指定的文件路径是否是本地的,没有则说明是HDFS上的文件路径。
        OVERWRITE:表示覆盖表中的已有数据。
        PARTITION ():如果是向分区表中导入数据的话需要指定分区。
2.实例:
    (1).无分区情况:
</code>

这里写图片描述

<code>    其中的'/root/data'可以是路径也可以是文件:
        路径表示把该路径下的所有文件都导入到表中;
        文件表示只把当前文件导入到表中。
    (2).有分区情况:
</code>

这里写图片描述

2、使用Sqoop进行数据的导入

<code>1.使用sqoop将mysql数据库中的数据导入到HDFS中
</code>
<code class=" hljs brainfuck"><span class="hljs-comment">hive</span>> <span class="hljs-comment">sqoop</span> <span class="hljs-comment">import</span> <span class="hljs-literal">-</span><span class="hljs-literal">-</span><span class="hljs-comment">connect</span> <span class="hljs-comment">jdbc:mysql://localhost/3306/sfd</span> <span class="hljs-literal">-</span><span class="hljs-literal">-</span><span class="hljs-comment">username</span> <span class="hljs-comment">root</span> <span class="hljs-literal">-</span><span class="hljs-literal">-</span><span class="hljs-comment">password</span> <span class="hljs-comment">123</span> <span class="hljs-literal">-</span><span class="hljs-literal">-</span><span class="hljs-comment">table</span> <span class="hljs-comment">student</span> <span class="hljs-literal">-</span><span class="hljs-literal">-</span><span class="hljs-comment">columns</span> <span class="hljs-comment">'sid</span><span class="hljs-string">,</span><span class="hljs-comment">sname'</span> <span class="hljs-literal">-</span><span class="hljs-comment">m</span> <span class="hljs-comment">1</span> <span class="hljs-literal">-</span><span class="hljs-literal">-</span><span class="hljs-comment">target</span><span class="hljs-literal">-</span><span class="hljs-comment">dir</span> <span class="hljs-comment">'/sqoop/student'</span></code>
<code>    其中:
        --connet :表示数据库的url链接
        --username :数据库用户名
        --password :数据库用户密码
        --table :源数据所在的表
        --clomns : 表中的列名,(例子中使用',' 链接)
        -m 1 : 表示启用的mapreduce个数为1个
        --target-dir : 将源数据导入到HDFS上的那个文件夹下

2.使用sqoop将mysql数据库中的数据导入到hive中:
</code>
<code class=" hljs brainfuck"><span class="hljs-comment">hive</span>> <span class="hljs-comment">sqoop</span> <span class="hljs-comment">import</span> <span class="hljs-literal">-</span><span class="hljs-literal">-</span><span class="hljs-comment">hive</span><span class="hljs-literal">-</span><span class="hljs-comment">import</span> <span class="hljs-literal">-</span><span class="hljs-literal">-</span><span class="hljs-comment">connect</span> <span class="hljs-comment">jdbc:mysql://localhost/3306/sfd</span> <span class="hljs-literal">-</span><span class="hljs-literal">-</span><span class="hljs-comment">username</span> <span class="hljs-comment">root</span> <span class="hljs-literal">-</span><span class="hljs-literal">-</span><span class="hljs-comment">password</span> <span class="hljs-comment">123</span> <span class="hljs-literal">-</span><span class="hljs-literal">-</span><span class="hljs-comment">table</span> <span class="hljs-comment">student</span> <span class="hljs-literal">-</span><span class="hljs-literal">-</span><span class="hljs-comment">columns</span> <span class="hljs-comment">'sid</span><span class="hljs-string">,</span><span class="hljs-comment">sname'</span> <span class="hljs-literal">-</span><span class="hljs-comment">m</span> <span class="hljs-comment">1</span> <span class="hljs-literal">-</span><span class="hljs-literal">-</span><span class="hljs-comment">hive</span><span class="hljs-literal">-</span><span class="hljs-comment">table</span> <span class="hljs-comment">stu</span> <span class="hljs-literal">-</span><span class="hljs-literal">-</span><span class="hljs-comment">where</span> <span class="hljs-comment">'sid=1'</span></code>
<code>    其中:
        --hive-table stu : 表示在导入到hive中名为stu的表中
        --where :表示插入数据的条件

3.使用sqoop将mysql数据库中的数据导入到hive中,并使用查询语句;
</code>
<code class=" hljs brainfuck"><span class="hljs-comment">hive</span>> <span class="hljs-comment">sqoop</span> <span class="hljs-comment">import</span> <span class="hljs-literal">-</span><span class="hljs-literal">-</span><span class="hljs-comment">hive</span><span class="hljs-literal">-</span><span class="hljs-comment">import</span> <span class="hljs-literal">-</span><span class="hljs-literal">-</span><span class="hljs-comment">connect</span> <span class="hljs-comment">jdbc:mysql://localhost/3306/sfd</span> <span class="hljs-literal">-</span><span class="hljs-literal">-</span><span class="hljs-comment">username</span> <span class="hljs-comment">root</span> <span class="hljs-literal">-</span><span class="hljs-literal">-</span><span class="hljs-comment">password</span> <span class="hljs-comment">123</span> <span class="hljs-literal">-</span><span class="hljs-comment">m</span> <span class="hljs-comment">1</span> <span class="hljs-literal">-</span><span class="hljs-literal">-</span><span class="hljs-comment">query</span> <span class="hljs-comment">'select</span> <span class="hljs-comment">*</span> <span class="hljs-comment">from</span> <span class="hljs-comment">student</span> <span class="hljs-comment">where</span> <span class="hljs-comment">sid='1'</span> <span class="hljs-comment">and</span> <span class="hljs-comment">$CONDITIONS'</span> <span class="hljs-literal">-</span><span class="hljs-literal">-</span><span class="hljs-comment">target</span><span class="hljs-literal">-</span><span class="hljs-comment">dir</span> <span class="hljs-comment">'/sqoop/student1'</span> <span class="hljs-literal">-</span><span class="hljs-literal">-</span><span class="hljs-comment">hive</span><span class="hljs-literal">-</span><span class="hljs-comment">table</span> <span class="hljs-comment">stu</span> </code>
<code>    其中:
        --query : 表示使用的查询语句,如果查询语句中有where条件限制那么必须加上 and $CONDITIONS(大写)

4.使用sqoop将hive中的数据导出到mysql中:
</code>
<code class=" hljs brainfuck"><span class="hljs-comment">hive</span>> <span class="hljs-comment">sqoop</span> <span class="hljs-comment">export</span> <span class="hljs-literal">-</span><span class="hljs-literal">-</span><span class="hljs-comment">connect</span> <span class="hljs-comment">jdbc:mysql://localhost/3306/sfd</span> <span class="hljs-literal">-</span><span class="hljs-literal">-</span><span class="hljs-comment">username</span> <span class="hljs-comment">root</span> <span class="hljs-literal">-</span><span class="hljs-literal">-</span><span class="hljs-comment">password</span> <span class="hljs-comment">123</span> <span class="hljs-literal">-</span><span class="hljs-comment">m</span> <span class="hljs-comment">1</span> <span class="hljs-literal">-</span><span class="hljs-literal">-</span><span class="hljs-comment">table</span> <span class="hljs-comment">student1</span> <span class="hljs-literal">-</span><span class="hljs-literal">-</span><span class="hljs-comment">export</span><span class="hljs-literal">-</span><span class="hljs-comment">dir</span> <span class="hljs-comment">'/data'</span></code>
<code>    其中:
        --table :为mysql数据库中的已经建立了的表
        --export-dir :将数据这个文件夹下的数据导入到mysql的student1表中。
</code>

二、Hive的数据查询

1、 查询的语法:

这里写图片描述

<code>    例子:查询student表中的信息:
        select * from student;(查询所有信息不用启用mapreduce)
        select sid from student;(需要启动mapreduce)
        select sid,sname,math,english,math+english from student;(在(math+english)表达式中如果有一个变量为空那么整个表达式为空,可以使用nvl(math,0)函数,表示如果math为空令其为0)
</code>

这里写图片描述

2、简单查询的Fetch Task功能,

<code>从上面的例子中可以看出,简单的查询如果不是查询所有的信息,就会开启mapreduce任务,这样会影响工作效率,从Hive0.10.0版本开始支持了Fetch Task功能;
Fetch Task功能配置方式:
    a. 方式一: set hive.fetch.task.conversion=more
    b. 方式二: hive --hiveconf hive.fetch.task.conversion=more
    c. 方式三: 修改hive-site.xml文件
</code>

这里写图片描述

<code>    前两种方式只在当前hive命令行有用,当重启hive时简单查询还是会调用mapreduce程序;而第二种方式配置是一直起作用的。
</code>

3.、在查询中使用过滤

<code>1.where 语句进行过滤。(字符串过滤区分大小写)
</code>

这里写图片描述

<code>    其中:%\\_%  :  由于_是模糊查询中的关键词(表示有一个字符),所以要用到转义字符,第一个'\'表示后面使用的是转义字符,'\_'表示的是'_';
</code>

4、在查询中排序

排序默认是升序的,要想降序只需在末尾加上desc
这里写图片描述

注意:当使用序号进行排序的使用需要设置一个属性:set hive.groupby.orderby.position.alias=true;


三、Hive的内置函数

这里写图片描述

1、数学函数:

<code>round(45.926,2):四舍五入(第二个参数表示的是保留小数点后面几位,当参数为负数是表示的是小数点前)
</code>

这里写图片描述
ceil(45.9):向上取整
floor(45.9):向下取整

2、字符函数:

<code>lower:把字符串转换成小写
upper:把字符串装换成大写
length:字符串的长度
concat('hello','world'):添加一个字符串
substr(a,b):截取字符串:(从a中,第b为开始取,取到右边所有的字符)
substr(a,b,c):截取字符串:(从a中,第b为开始取,取c个字符)
trim:去掉字符串两端的空格
lpad('abc',10,'*'):左填充
rpad:右填充
</code>

3、收集函数和转换函数:

<code>1,收集函数:
    size:
</code>

这里写图片描述

<code>2,转换函数:
    cast:cast(1 as bigint);
</code>

4、日期函数:

<code>to_data:取出字符串中的日期部分
</code>

这里写图片描述

<code>year:取出日期中的年
month:取出日期中的月
day:取出日期中的日
</code>

这里写图片描述

<code>weekofyear:返回一个日期在一年中是第几个星期
</code>

这里写图片描述

<code>datediff:两个日期相减返回相差的天数
</code>

这里写图片描述

<code>date_add:在一个日期上加上多少天
date_sub:在一个日期上减去多少天
</code>

这里写图片描述

5、条件函数:

<code>coalesce(a,b,...):从做到右返回第一个不为null的值
</code>

这里写图片描述

<code>case...when...: 条件表达式
    case a when b then c [when d then e]* [else f] end
</code>

这里写图片描述

6、聚合函数:

<code>count:个数
sum:求和
min:求最小值
max:求最大值
avg:求平均值
</code>

7、表生成函数:

<code>explode:把一个map集合或者是array数组中的一个元素单独生成一行
</code>

这里写图片描述
这里写图片描述

数据仓库—-hive进阶篇二

Statement
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Explain the ACID properties (Atomicity, Consistency, Isolation, Durability).Explain the ACID properties (Atomicity, Consistency, Isolation, Durability).Apr 16, 2025 am 12:20 AM

ACID attributes include atomicity, consistency, isolation and durability, and are the cornerstone of database design. 1. Atomicity ensures that the transaction is either completely successful or completely failed. 2. Consistency ensures that the database remains consistent before and after a transaction. 3. Isolation ensures that transactions do not interfere with each other. 4. Persistence ensures that data is permanently saved after transaction submission.

MySQL: Database Management System vs. Programming LanguageMySQL: Database Management System vs. Programming LanguageApr 16, 2025 am 12:19 AM

MySQL is not only a database management system (DBMS) but also closely related to programming languages. 1) As a DBMS, MySQL is used to store, organize and retrieve data, and optimizing indexes can improve query performance. 2) Combining SQL with programming languages, embedded in Python, using ORM tools such as SQLAlchemy can simplify operations. 3) Performance optimization includes indexing, querying, caching, library and table division and transaction management.

MySQL: Managing Data with SQL CommandsMySQL: Managing Data with SQL CommandsApr 16, 2025 am 12:19 AM

MySQL uses SQL commands to manage data. 1. Basic commands include SELECT, INSERT, UPDATE and DELETE. 2. Advanced usage involves JOIN, subquery and aggregate functions. 3. Common errors include syntax, logic and performance issues. 4. Optimization tips include using indexes, avoiding SELECT* and using LIMIT.

MySQL's Purpose: Storing and Managing Data EffectivelyMySQL's Purpose: Storing and Managing Data EffectivelyApr 16, 2025 am 12:16 AM

MySQL is an efficient relational database management system suitable for storing and managing data. Its advantages include high-performance queries, flexible transaction processing and rich data types. In practical applications, MySQL is often used in e-commerce platforms, social networks and content management systems, but attention should be paid to performance optimization, data security and scalability.

SQL and MySQL: Understanding the RelationshipSQL and MySQL: Understanding the RelationshipApr 16, 2025 am 12:14 AM

The relationship between SQL and MySQL is the relationship between standard languages ​​and specific implementations. 1.SQL is a standard language used to manage and operate relational databases, allowing data addition, deletion, modification and query. 2.MySQL is a specific database management system that uses SQL as its operating language and provides efficient data storage and management.

Explain the role of InnoDB redo logs and undo logs.Explain the role of InnoDB redo logs and undo logs.Apr 15, 2025 am 12:16 AM

InnoDB uses redologs and undologs to ensure data consistency and reliability. 1.redologs record data page modification to ensure crash recovery and transaction persistence. 2.undologs records the original data value and supports transaction rollback and MVCC.

What are the key metrics to look for in an EXPLAIN output (type, key, rows, Extra)?What are the key metrics to look for in an EXPLAIN output (type, key, rows, Extra)?Apr 15, 2025 am 12:15 AM

Key metrics for EXPLAIN commands include type, key, rows, and Extra. 1) The type reflects the access type of the query. The higher the value, the higher the efficiency, such as const is better than ALL. 2) The key displays the index used, and NULL indicates no index. 3) rows estimates the number of scanned rows, affecting query performance. 4) Extra provides additional information, such as Usingfilesort prompts that it needs to be optimized.

What is the Using temporary status in EXPLAIN and how to avoid it?What is the Using temporary status in EXPLAIN and how to avoid it?Apr 15, 2025 am 12:14 AM

Usingtemporary indicates that the need to create temporary tables in MySQL queries, which are commonly found in ORDERBY using DISTINCT, GROUPBY, or non-indexed columns. You can avoid the occurrence of indexes and rewrite queries and improve query performance. Specifically, when Usingtemporary appears in EXPLAIN output, it means that MySQL needs to create temporary tables to handle queries. This usually occurs when: 1) deduplication or grouping when using DISTINCT or GROUPBY; 2) sort when ORDERBY contains non-index columns; 3) use complex subquery or join operations. Optimization methods include: 1) ORDERBY and GROUPB

See all articles

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

AI Hentai Generator

AI Hentai Generator

Generate AI Hentai for free.

Hot Article

R.E.P.O. Energy Crystals Explained and What They Do (Yellow Crystal)
4 weeks agoBy尊渡假赌尊渡假赌尊渡假赌
R.E.P.O. Best Graphic Settings
4 weeks agoBy尊渡假赌尊渡假赌尊渡假赌
R.E.P.O. How to Fix Audio if You Can't Hear Anyone
1 months agoBy尊渡假赌尊渡假赌尊渡假赌
R.E.P.O. Chat Commands and How to Use Them
1 months agoBy尊渡假赌尊渡假赌尊渡假赌

Hot Tools

ZendStudio 13.5.1 Mac

ZendStudio 13.5.1 Mac

Powerful PHP integrated development environment

PhpStorm Mac version

PhpStorm Mac version

The latest (2018.2.1) professional PHP integrated development tool

SecLists

SecLists

SecLists is the ultimate security tester's companion. It is a collection of various types of lists that are frequently used during security assessments, all in one place. SecLists helps make security testing more efficient and productive by conveniently providing all the lists a security tester might need. List types include usernames, passwords, URLs, fuzzing payloads, sensitive data patterns, web shells, and more. The tester can simply pull this repository onto a new test machine and he will have access to every type of list he needs.

DVWA

DVWA

Damn Vulnerable Web App (DVWA) is a PHP/MySQL web application that is very vulnerable. Its main goals are to be an aid for security professionals to test their skills and tools in a legal environment, to help web developers better understand the process of securing web applications, and to help teachers/students teach/learn in a classroom environment Web application security. The goal of DVWA is to practice some of the most common web vulnerabilities through a simple and straightforward interface, with varying degrees of difficulty. Please note that this software

VSCode Windows 64-bit Download

VSCode Windows 64-bit Download

A free and powerful IDE editor launched by Microsoft