hive学习笔记之二:复杂数据类型,五分钟搞懂MySQL主从复制原理

HarmonyOS学习 2021-11-25 18:30:01
java 面试 编程语言 后端开发

jerry [“jerry_friend_0”,“jerry_friend_1”,“jerry_friend_2”,“jerry_friend_3”,“jerry_friend_4”,“jerry_friend_5”]

Time taken: 0.052 seconds, Fetched: 2 row(s)

  1. 查询friends中的某个元素的SQL:

select person, friends[0], friends[3] from t2;

执行结果如下,第一条记录没有friends[3],显示为NULL:

hive> select person, friends[0], friends[3] from t2;

OK

person _c1 _c2

tom tom_friend_0 NULL

jerry jerry_friend_0 jerry_friend_3

Time taken: 0.052 seconds, Fetched: 2 row(s)

  1. 数组元素中是否包含某值的SQL:

select person, array_contains(friends, ‘tom_friend_0’) from t2;

执行结果如下,第一条记录friends数组中有tom_friend_0,显示为true,第二条记录不包含,就显示false:

hive> select person, array_contains(friends, ‘tom_friend_0’) from t2;

OK

person _c1

tom true

jerry false

Time taken: 0.061 seconds, Fetched: 2 row(s)

  1. 第一条记录的friends数组中有三个元素,借助LATERAL VIEW语法可以把这三个元素拆成三行,SQL如下:

select t.person, single_friend

from (

select person, friends

from t2 where person=‘tom’

) t LATERAL VIEW explode(t.friends) v as single_friend;

执行结果如下,可见数组中的每个元素都能拆成单独一行:

OK

t.person single_friend

tom tom_friend_0

tom tom_friend_1

tom tom_friend_2

Time taken: 0.058 seconds, Fetched: 3 row(s)

  • 以上就是数组的基本操作,接下来是键值对;

 MAP,建表,导入数据

  • 接下来打算创建名为t3的表,只有person和address两个字段,person是字符串类型,address是MAP类型,通过文本文件导入数据时,对分隔符的定义如下:
  1. person和address之间的分隔符是竖线;

  2. address内部有多个键值对,它们的分隔符是逗号;

  3. 而每个键值对的键和值的分隔符是冒号;

  • 满足上述要求的建表语句如下所示:

create table if not exists t3(

person string,

address map<string, string>

)

row format delimited

fields terminated by ‘|’

collection items terminated by ‘,’

map keys terminated by ‘:’;

  • 创建文本文件003.txt,可见用了三种分隔符来分隔字段、MAP中的多个元素、每个元素键和值:

tom|province:guangdong,city:shenzhen

jerry|province:jiangsu,city:nanjing

  • 导入003.txt的数据到t3表:

load data local inpath ‘/home/hadoop/temp/202010/25/003.txt’ into table t3;

 MAP,查询

  1. 查看全部数据:

hive> select * from t3;

OK

t3.person t3.address

tom {“province”:“guangdong”,“city”:“shenzhen”}

jerry {“province”:“jiangsu”,“city”:“nanjing”}

Time taken: 0.075 seconds, Fetched: 2 row(s)

  1. 查看MAP中的某个key,语法是field[“xxx”]:

hive> select person, address[“province”] from t3;

OK

person _c1

tom guangdong

jerry jiangsu

Time taken: 0.075 seconds, Fetched: 2 row(s)

  1. 使用if函数,下面的SQL是判断address字段中是否有"street"键,如果有就显示对应的值,没有就显示filed street not exists:

select person,

if(address[‘street’] is null, “filed street not exists”, address[‘street’])

from t3;

输出如下,由于address字段只有province和city两个键,因此会显示filed street not exists:

OK

tom filed street not exists

jerry filed street not exists

Time taken: 0.087 seconds, Fetched: 2 row(s)

  1. 使用explode将address字段的每个键值对展示成一行:

hive> select explode(address) from t3;

OK

province guangdong

city shenzhen

province jiangsu

city nanjing

Time taken: 0.081 seconds, Fetched: 4 row(s)

  1. 上面的explode函数只能展示address字段,如果还要展示其他字段就要继续LATERAL VIEW语法,如下,可见前面的数组展开为一个字段,MAP展开为两个字段,分别是key和value:

select t.person, address_key, address_value

from (

select person, address

from t3 where person=‘tom’

) t LATERAL VIEW explode(t.address) v as address_key, address_value;

结果如下:

OK

tom province guangdong

tom city shenzhen

Time taken: 0.118 seconds, Fetc

《一线大厂Java面试题解析+后端开发学习笔记+最新架构讲解视频+实战项目源码讲义》

【docs.qq.com/doc/DSmxTbFJ1cmN1R2dB】 完整内容开源分享

hed: 2 row(s)

  1. size函数可以查看MAP中键值对的数量:

hive> select person, size(address) from t3;

OK

tom 2

jerry 2

Time taken: 0.082 seconds, Fetched: 2 row(s)

 STRUCT

  1. STRUCT是一种记录类型,它封装了一个命名的字段集合,里面有很多属性,新建名为t4的表,其info字段就是STRUCT类型,里面有age和city两个属性,person和info之间的分隔符是竖线,info内部的多个元素之间的分隔符是逗号,注意声明分隔符的语法:

create table if not exists t4(

person string,

info struct<age:int, city:string>

)

row format delimited

fields terminated by ‘|’

collection items terminated by ‘,’;

  1. 准备好名为004.txt的文本文件,内容如下:

tom|11,shenzhen

jerry|12,nanjing

  1. 加载004.txt的数据到t4表:

load data local inpath ‘/home/hadoop/temp/202010/25/004.txt’ into table t4;

  1. 查看t4的所有数据:

hive> select * from t4;

OK

tom {“age”:11,“city”:“shenzhen”}

jerry {“age”:12,“city”:“nanjing”}

Time taken: 0.063 seconds, Fetched: 2 row(s)

  1. 查看指定字段,用filedname.xxx语法:

hive> select person, info.city from t4;

OK

tom shenzhen

jerry nanjing

Time taken: 0.141 seconds, Fetched: 2 row(s)

一线互联网大厂Java核心面试题库

hive学习笔记之二:复杂数据类型,五分钟搞懂MySQL主从复制原理_Java

正逢面试跳槽季,给大家整理了大厂问到的一些面试真题,由于文章长度限制,只给大家展示了部分题目,更多Java基础、异常、集合、并发编程、JVM、Spring全家桶、MyBatis、Redis、数据库、中间件MQ、Dubbo、Linux、Tomcat、ZooKeeper、Netty等等已整理上传,感兴趣的朋友可以看看支持一波!

本文已被 CODING开源项目:【一线大厂Java面试题解析+核心总结学习笔记+最新讲解视频+实战项目源码】收录

版权声明
本文为[HarmonyOS学习]所创,转载请带上原文链接,感谢
https://blog.51cto.com/u_15438507/4690475

  1. Java经典面试题详解,突围金九银十面试季(附详细答案,mysql集群架构部署方案
  2. java整理,java高级特性编程及实战第一章
  3. java教程——反射,mongodb下载教程
  4. Java岗大厂面试百日冲刺 - 日积月累,每日三题【Day12,zookeeper原理作用
  5. Java后端互联网500道中高级面试题(含答案),linux钩子技术
  6. java8 Stream API及常用方法,java初级程序员面试
  7. java-集合-Map(双列)——迪迦重制版,2021Java开发社招面试解答之性能优化
  8. Flink处理函数实战之二:ProcessFunction类,java线程面试题目
  9. flex 布局详解,【Java面试题
  10. Linux basic command learning
  11. Why did docker lose to kubernetes? Docker employee readme!
  12. MySQL安装
  13. Elastic Search Aggregate Learning five: Problem Analysis of Uncertainty of sequencing results, Alibaba Java Performance Tuning Practical
  14. Installing, configuring, starting and accessing rabbitmq under Linux
  15. Oracle SQL injection summary
  16. Installation MySQL
  17. L'exposition à la photo d'essai sur la route i7 du nouveau vaisseau amiral de BMW Pure Electric a également été comparée à celle de Xiaopeng p7.
  18. spring JTA 关于异常处理的时机问题
  19. Le problème du temps de traitement des exceptions dans la JTA printanière
  20. Flink Handling Function Real War II: processfunction class, Java thread interview subject
  21. Oracle SQL injection summary
  22. [Java data structure] you must master the classic example of linked list interview (with super detailed illustration and code)
  23. Do you really know MySQL order by
  24. Record a java reference passing problem
  25. spring JTA 關於异常處理的時機問題
  26. Java - Set - Map (double file) - dija Rewriting, 2021 Java Developer's Performance Optimization
  27. Android入门教程 | OkHttp + Retrofit 取消请求的方法
  28. Java 8 Stream API and common methods, Java Junior Program interview
  29. Github 疯传!史上最强!BAT 大佬,2021年最新Java大厂面试笔试题分享
  30. git(3)Git 分支,zookeeper下载教程
  31. Java Backend Internet 500 questions d'entrevue moyennes et avancées (y compris les réponses), technologie de crochet Linux
  32. Entretien d'entretien d'usine Java post sprint de 100 jours - accumulation de jours et de mois, trois questions par jour [jour 12, fonction de principe de Zookeeper
  33. Tutoriel Java - reflection, tutoriel de téléchargement mongodb
  34. How to analyze several common key and hot issues in redis from multiple dimensions
  35. GIT (3) GIT Branch, Zookeeper Download tutoriel
  36. Tutoriel de démarrage Android | okhttp + Retrofit comment annuler une demande
  37. Design pattern [3.3] - Interpretation of cglib dynamic agent source code
  38. Share the actual operation of private collection project nodejs backend + Vue + Mysql to build a management system
  39. Springboot has 44 application initiators
  40. GitHub上霸榜久居不下的《Java面试突击宝典》,java图形用户界面设计基础
  41. GitHub上访问下载破百万的神仙文档《Java面试神技》看完我呆了,java面试问项目中遇到的问题
  42. GitHub上标星75k 超牛的《Java面试突击版,java高级工程师技能
  43. GitHub上标星2,java项目开发实训教程
  44. Docker development environment Preview
  45. JavaScript高級深入淺出:掌握 this 指向
  46. JavaScript Advanced Insight and outside: Mastering this direction
  47. Vue de l'application pratique de Javascript, drop drag Event
  48. docker 安装部署 Jenkins 2.322
  49. kafka安装
  50. 近九万字图文详解RabbitMQ
  51. Engaged in Java for one and a half years, how to break through yourself
  52. 输出9*9乘法表----java
  53. 判断一个数是不是素数-------java
  54. java项目,记录页面修改值,内部打“官司”用
  55. Docker installation Deployment Jenkins 2.322
  56. Comment porter un pantalon en hiver? Les petits hommes, les jambes épaisses et la largeur de l'entrejambe peuvent être vus. 3 techniques pour éviter la foudre
  57. MySQL下载和安装教程
  58. In depth analysis of rocketmq source code - message storage module
  59. Spring transaction management
  60. mysql恢复ibd数据,为何频频报错?