Tham gia Hive & Hướng dẫn truy vấn phụ với các ví dụ

Mục lục:

Anonim

Trong hướng dẫn này, bạn sẽ học-

  • Tham gia truy vấn
  • Các loại liên kết khác nhau
  • Truy vấn phụ
  • Nhúng các tập lệnh tùy chỉnh
  • UDF (Chức năng do người dùng xác định)

Tham gia các truy vấn:

Các truy vấn tham gia có thể thực hiện trên hai bảng có trong Hive. Để hiểu rõ hơn về các khái niệm liên kết, ở đây, chúng tôi sẽ tạo hai bảng ở đây,

  • Sample_joins (Liên quan đến Chi tiết về Khách hàng)
  • Sample_joins1 (Liên quan đến chi tiết đơn hàng do Nhân viên thực hiện)

Bước 1) Tạo bảng "sample_joins" với các cột tên ID, Tên, Tuổi, địa chỉ và mức lương của nhân viên

Bước 2) Tải và hiển thị dữ liệu

Từ ảnh chụp màn hình trên

  1. Đang tải dữ liệu vào sample_joins từ Customer.txt
  2. Hiển thị nội dung bảng sample_joins

Bước 3) Tạo bảng sample_joins1 và tải, hiển thị dữ liệu

Từ ảnh chụp màn hình trên, chúng ta có thể quan sát những điều sau

  1. Tạo bảng sample_joins1 với các cột Orderid, Date1, Id, Amount
  2. Đang tải dữ liệu vào sample_joins1 từ order.txt
  3. Hiển thị các bản ghi có trong sample_joins1

Tiếp theo, chúng ta sẽ thấy các kiểu nối khác nhau có thể được thực hiện trên các bảng mà chúng ta đã tạo nhưng trước đó bạn phải xem xét các điểm sau cho các phép nối.

Một số điểm cần lưu ý trong Tham gia:

  • Chỉ các phép tham gia Bình đẳng mới được phép Trong các lần tham gia
  • Nhiều hơn hai bảng có thể được kết hợp trong cùng một truy vấn
  • Các phép nối LEFT, RIGHT, FULL OUTER tồn tại để cung cấp nhiều quyền kiểm soát hơn đối với Mệnh đề BẬT mà không có điều khoản nào phù hợp
  • Các phép tham gia không mang tính chất giao hoán
  • Các phép tham gia được liên kết bên trái bất kể chúng là phép tham gia TRÁI hay PHẢI

Các loại liên kết khác nhau

Tham gia có 4 loại, đây là

  • Tham gia bên trong
  • Tham gia bên ngoài bên trái
  • Tham gia bên ngoài bên phải
  • Tham gia đầy đủ bên ngoài

Tham gia bên trong:

Các Bản ghi chung cho cả hai bảng sẽ được truy xuất bởi Tham gia Bên trong này.

Từ ảnh chụp màn hình trên, chúng ta có thể quan sát những điều sau

  1. Ở đây chúng tôi đang thực hiện truy vấn nối bằng cách sử dụng từ khóa JOIN giữa các bảng sample_joins và sample_joins1 với điều kiện phù hợp là (c.Id = o.Id).
  2. Kết quả hiển thị các bản ghi chung có trong cả bảng bằng cách kiểm tra điều kiện được đề cập trong truy vấn

Truy vấn:

SELECT c.Id, c.Name, c.Age, o.Amount FROM sample_joins c JOIN sample_joins1 o ON(c.Id=o.Id);

Tham gia bên ngoài bên trái:

  • Ngôn ngữ truy vấn Hive LEFT OUTER JOIN trả về tất cả các hàng từ bảng bên trái mặc dù không có hàng nào phù hợp trong bảng bên phải
  • Nếu Mệnh đề ON khớp với 0 bản ghi trong bảng bên phải, các phép nối vẫn trả về một bản ghi trong kết quả với NULL trong mỗi cột từ bảng bên phải

Từ ảnh chụp màn hình trên, chúng ta có thể quan sát những điều sau

  1. Ở đây chúng tôi đang thực hiện truy vấn nối bằng từ khóa "LEFT OUTER JOIN" giữa các bảng sample_joins và sample_joins1 với điều kiện khớp là (c.Id = o.Id).

    Ví dụ ở đây chúng tôi đang sử dụng id nhân viên làm tham chiếu, nó kiểm tra xem id có phổ biến ở bên phải cũng như bên trái bảng hay không. Nó hoạt động như một điều kiện phù hợp.

  2. Đầu ra hiển thị các bản ghi chung có trong cả bảng bằng cách kiểm tra điều kiện được đề cập trong truy vấn.

    Giá trị NULL trong đầu ra ở trên là các cột không có giá trị nào từ Bảng bên phải là sample_joins1

Truy vấn:

SELECT c.Id, c.Name, o.Amount, o.Date1 FROM sample_joins c LEFT OUTER JOIN sample_joins1 o ON(c.Id=o.Id)

Tham gia bên ngoài bên phải:

  • Ngôn ngữ truy vấn Hive RIGHT OUTER JOIN trả về tất cả các hàng từ bảng Bên phải mặc dù không có hàng nào phù hợp trong bảng bên trái
  • Nếu Mệnh đề ON khớp với 0 bản ghi trong bảng bên trái, các phép nối vẫn trả về một bản ghi trong kết quả với NULL trong mỗi cột từ bảng bên trái
  • Các phép nối RIGHT luôn trả về các bản ghi từ bảng Bên phải và các bản ghi phù hợp từ bảng bên trái. Nếu bảng bên trái không có giá trị nào tương ứng với cột, nó sẽ trả về giá trị NULL ở vị trí đó.

Từ ảnh chụp màn hình trên, chúng ta có thể quan sát những điều sau

  1. Ở đây chúng tôi đang thực hiện truy vấn nối bằng từ khóa "RIGHT OUTER JOIN" giữa các bảng sample_joins và sample_joins1 với điều kiện khớp là (c.Id = o.Id).
  2. Kết quả hiển thị các bản ghi chung có trong cả bảng bằng cách kiểm tra điều kiện được đề cập trong truy vấn

Truy vấn :

 SELECT c.Id, c.Name, o.Amount, o.Date1 FROM sample_joins c RIGHT OUTER JOIN sample_joins1 o ON(c.Id=o.Id)

Tham gia đầy đủ bên ngoài:

Nó kết hợp các bản ghi của cả bảng sample_joins và sample_joins1 dựa trên Điều kiện JOIN được đưa ra trong truy vấn.

Nó trả về tất cả các bản ghi từ cả hai bảng và điền vào Giá trị NULL cho các cột thiếu giá trị khớp ở hai bên.

Từ ảnh chụp màn hình trên, chúng ta có thể quan sát những điều sau:

  1. Ở đây chúng tôi đang thực hiện truy vấn nối bằng cách sử dụng từ khóa "FULL OUTER JOIN" giữa các bảng sample_joins và sample_joins1 với điều kiện khớp là (c.Id = o.Id).
  2. Kết quả hiển thị tất cả các bản ghi có trong cả bảng bằng cách kiểm tra điều kiện được đề cập trong truy vấn. Giá trị rỗng ở đầu ra ở đây cho biết các giá trị bị thiếu từ các cột của cả hai bảng.

Truy vấn

SELECT c.Id, c.Name, o.Amount, o.Date1 FROM sample_joins c FULL OUTER JOIN sample_joins1 o ON(c.Id=o.Id)

Truy vấn phụ:

Một Truy vấn hiện trong một Truy vấn được gọi là một truy vấn phụ. Truy vấn chính sẽ phụ thuộc vào các giá trị được trả về bởi các truy vấn phụ.

Truy vấn con có thể được phân thành hai loại

  • Truy vấn con trong mệnh đề FROM
  • Truy vấn con trong mệnh đề WHERE

Khi nào sử dụng:

  • Để nhận một giá trị cụ thể được kết hợp từ hai giá trị cột từ các bảng khác nhau
  • Sự phụ thuộc của một giá trị bảng trên các bảng khác
  • Kiểm tra so sánh các giá trị của một cột từ các bảng khác

Cú pháp:

Subquery in FROM clauseSELECT From (SubQuery) Subquery in WHERE clauseSELECT  FromWHERE col1 IN (SubQuery);

Thí dụ:

SELECT col1 FROM (SELECT a+b AS col1 FROM t1) t2

Ở đây t1 và t2 là tên bảng. Màu được tô màu là Truy vấn con được thực hiện trên bảng t1. Ở đây a và b là các cột được thêm vào trong một truy vấn con và được gán cho col1. Col1 là giá trị cột có trong bảng Chính. Cột "col1" này có trong truy vấn con tương đương với truy vấn bảng chính trong cột col1.

Nhúng các tập lệnh tùy chỉnh:

Hive cung cấp tính khả thi của việc viết các kịch bản cụ thể của người dùng cho các yêu cầu của khách hàng. Người dùng có thể viết bản đồ của riêng họ và giảm bớt các tập lệnh cho các yêu cầu. Chúng được gọi là các tập lệnh tùy chỉnh được nhúng. Logic mã hóa được xác định trong các tập lệnh tùy chỉnh và chúng ta có thể sử dụng tập lệnh đó trong thời gian ETL.

Khi nào chọn Tập lệnh được nhúng:

  • Trong các yêu cầu cụ thể của khách hàng, các nhà phát triển phải viết và triển khai các tập lệnh trong Hive
  • Trường hợp các chức năng có sẵn của Hive sẽ không hoạt động cho các yêu cầu miền cụ thể

Đối với điều này trong Hive, nó sử dụng mệnh đề TRANSFORM để nhúng cả bản đồ và tập lệnh trình thu gọn.

Trong tập lệnh tùy chỉnh được nhúng này, chúng ta phải quan sát các điểm sau

  • Các cột sẽ được chuyển đổi thành chuỗi và được phân tách bằng TAB trước khi đưa nó cho tập lệnh người dùng
  • Đầu ra tiêu chuẩn của tập lệnh người dùng sẽ được coi là các cột chuỗi được phân tách bằng TAB

Tập lệnh nhúng mẫu,

FROM (FROM pv_usersMAP pv_users.userid, pv_users.dateUSING 'map_script'AS dt, uidCLUSTER BY dt) map_outputINSERT OVERWRITE TABLE pv_users_reducedREDUCE map_output.dt, map_output.uidUSING 'reduce_script'AS date, count;

Từ đoạn script trên, chúng ta có thể quan sát những điều sau

Đây chỉ là kịch bản mẫu để hiểu

  • pv_users là bảng người dùng có các trường như userid và ngày tháng như đã đề cập trong map_script
  • Tập lệnh giảm thiểu được xác định vào ngày và số lượng của bảng pv_users