Pip cài đặt spark-excel

Các nền tảng Dữ liệu và Phân tích khác nhau trên Azure hỗ trợ một số phương pháp độc đáo để thiết kế quy trình và triển khai các đường ống để Trích xuất, Tải và Chuyển đổi (ELT) dữ liệu của bạn. Với Azure Data Factory và Synapse Pipelines, các tác vụ và hoạt động do GUI điều khiển giúp đơn giản hóa việc thiết kế, triển khai và thời gian đưa ra thị trường cho các quy trình và công việc ELT của bạn. Ngoài ra, Luồng dữ liệu ánh xạ và Đường ống dẫn khớp của ADF tận dụng công cụ tính toán của Apache Spark dưới mui xe

Công cụ phân tích hợp nhất của Apache Spark là một công cụ thay đổi cuộc chơi thị trường vì nó có khả năng xử lý dữ liệu quy mô lớn và mang lại lợi ích kiến ​​trúc của bộ dữ liệu phân tán linh hoạt (RDD) hỗ trợ khả năng chịu lỗi để phân phối dữ liệu trên các cụm máy. Mặc dù có nhiều hoạt động dựa trên GUI để nhập và chuyển đổi dữ liệu trong Data Factory và Synapse Analytics, nhưng đôi khi, các Kỹ sư dữ liệu đang xây dựng các quy trình ELT này cần linh hoạt hơn trong cách tiếp cận của họ để xây dựng các quy trình ELT này bằng cách có khả năng viết mã tùy chỉnh bằng nhiều ngôn ngữ

Dung dịch

Với không gian làm việc của Databricks và Synapse Analytics, hai Nền tảng phân tích và dữ liệu hợp nhất hàng đầu của Azure, bạn có thể viết mã tùy chỉnh cho công việc ELT của mình bằng nhiều ngôn ngữ trong cùng một sổ ghi chép. API của Apache Spark cung cấp giao diện cho các ngôn ngữ bao gồm Python, R, Scala và SQL. Ngoài ra, các nền tảng này cung cấp khả năng truy cập các thư viện nguồn mở và tùy chỉnh ở phạm vi cụm hoặc sổ ghi chép để cung cấp cho Kỹ sư dữ liệu các công cụ phù hợp để phát triển mã tùy chỉnh của họ nhằm điều chỉnh các quy trình và tập lệnh ELT của họ cho phù hợp với các tình huống và trường hợp sử dụng kinh doanh duy nhất. Mặc dù có thể viết mã tùy chỉnh bằng nhiều ngôn ngữ bằng cả Databricks và Synapse Analytics, nhưng trong bài viết này, bạn sẽ hiểu sâu hơn về cách viết mã tùy chỉnh hiệu quả trong PySpark, Scala và SQL cho khối lượng công việc ELT bằng sổ ghi chép Databricks

Có một số cách viết mã tùy chỉnh để trích xuất và tải dữ liệu từ nguồn đến đích trong Azure. Với sự hỗ trợ API của Spark cho nhiều ngôn ngữ khác nhau, ScalaSpark, SparkSQL và PySpark đều có thể được sử dụng trong cùng một sổ ghi chép khi cần. Ngoài ra, công cụ Delta cũng hỗ trợ các ngôn ngữ này. Trong bài viết này, bạn sẽ tìm hiểu về một vài trường hợp sử dụng để trích xuất và tải dữ liệu nguồn URL Excel, XML, JSON và Zip bằng mã PySpark tùy chỉnh. Đến cuối bài viết này, bạn sẽ tìm hiểu về cách bạn cũng có thể sử dụng Scala, SQL và Hàm do người dùng xác định (UDF) trong sổ ghi chép Databricks ELT của mình

PySpark

PySpark là một giao diện dành cho Apache Spark bằng Python, cho phép viết các ứng dụng Spark bằng API Python và cung cấp trình bao PySpark để phân tích dữ liệu một cách tương tác trong môi trường phân tán. PySpark hỗ trợ các tính năng bao gồm Spark SQL, DataFrame, Truyền phát, MLlib và Spark Core. Trong Azure, PySpark được sử dụng phổ biến nhất trong nền tảng Databricks, điều này làm cho nó trở nên tuyệt vời để thực hiện phân tích khám phá trên dữ liệu về khối lượng, giống và vận tốc. Nó cho phép người dùng xây dựng các quy trình học máy và tạo ELT cho Data Lakehouse. Các thư viện phổ biến như Pandas cùng với các thư viện tùy chỉnh có thể được tận dụng bằng các đường dẫn và phân tích PySpark. Người dùng thường gặp khó khăn khi bắt đầu viết mã PySpark chức năng và thường xuyên tìm kiếm các mẫu thành công khi bắt đầu với PySpark cho các công việc ELT của Lakehouse

PySpark được các Kỹ sư dữ liệu, Nhà khoa học dữ liệu và Nhà phân tích dữ liệu sử dụng rộng rãi để xử lý khối lượng công việc dữ liệu lớn. PySpark tuyệt vời vì nó hỗ trợ tính toán trong bộ nhớ, xử lý phân tán, khả năng chịu lỗi, tính bất biến, bộ nhớ đệm, đánh giá lười biếng, tối ưu hóa tích hợp và hỗ trợ ANSI SQL. Điều này làm cho các ứng dụng chạy trên PySpark nhanh hơn đáng kể so với các hệ thống truyền thống. Nó hỗ trợ liền mạch cả đường truyền phát trực tuyến và máy học, khiến nó trở thành lựa chọn phổ biến cho Kỹ sư dữ liệu và Nhà khoa học dữ liệu quan tâm đến việc làm việc với dữ liệu lớn của họ nằm trong Lakehouse. Databricks trên Azure đã được áp dụng rộng rãi như một công cụ tiêu chuẩn vàng để làm việc với Apache Spark do hỗ trợ mạnh mẽ cho PySpark. Trong phần này, bạn sẽ tìm hiểu các nguyên tắc cơ bản về viết mã PySpark chức năng trong Databricks để tạo cơ sở dữ liệu và bảng, đọc và ghi nhiều loại tệp, tạo hàm do người dùng xác định (UDF), làm việc với khung dữ liệu và Danh mục Spark, cùng với các chức năng khác

Phần sau đây sẽ trình bày cách trích xuất và tải các loại tệp nguồn URL Excel, XML, JSON và Zip

Excel

Với sổ ghi chép Databricks, bạn có thể phát triển mã tùy chỉnh để đọc và viết từ Excel (. xlsx) nguồn dữ liệu được lưu trữ trong tài khoản ADLSgen2 của bạn. Trước tiên, bạn cần đảm bảo rằng tài khoản ADLSgen2 của bạn được gắn vào không gian làm việc Databricks để dữ liệu của bạn có thể truy cập được từ sổ ghi chép của bạn. Trước khi bạn bắt đầu phát triển, hãy đảm bảo rằng cụm của bạn đã cài đặt thư viện Maven

csv.select("*").write.format('com.crealytics.spark.excel').option("header","true").option("inferSchema","true").save('/mnt/raw/dimdate.xlsx',mode="overwrite")

0 sau đây. Chỉ cần đi tới tab thư viện trên cụm của bạn, như thể hiện trong hình bên dưới và tìm kiếm

csv.select("*").write.format('com.crealytics.spark.excel').option("header","true").option("inferSchema","true").save('/mnt/raw/dimdate.xlsx',mode="overwrite")

1 trong nguồn thư viện Maven Central. Khi bạn tìm thấy thư viện nêu trên, hãy cài đặt nó trên cụm của bạn. Đối với bài tập này, tôi đã sử dụng phiên bản cụm sau

csv.select("*").write.format('com.crealytics.spark.excel').option("header","true").option("inferSchema","true").save('/mnt/raw/dimdate.xlsx',mode="overwrite")

2

Tiếp theo, tạo một sổ ghi chép Python mới và đảm bảo rằng cụm mà bạn đã tạo trước đó được đính kèm với nó. Mã PySpark hiển thị trong hình bên dưới sẽ gọi thư viện Maven Spark Excel và sẽ tải tệp Đơn đặt hàng Excel vào một khung dữ liệu. Lưu ý các tùy chọn khác nhau mà bạn có tùy ý sử dụng, bao gồm khả năng chỉ định tiêu đề, tên trang tính, v.v. Bạn cũng có thể chỉ định phạm vi ô bằng tùy chọn dataAddress

Đây là mã PySpark mà bạn sẽ cần chạy để tạo lại các kết quả được hiển thị trong hình trên

excel=spark.read.format("com.crealytics.spark.excel").option("header", "true").option("sheetName", "Orders").option("dataAddress", "'Orders'!A1:F600").option("inferSchema", "true").option("useHeader", "true").option("treatEmptyValuesAsNulls", "true").option("addColorColumns", "False").load("/mnt/raw/Orders.xlsx")

Sau khi chạy code thành công chạy lệnh

csv.select("*").write.format('com.crealytics.spark.excel').option("header","true").option("inferSchema","true").save('/mnt/raw/dimdate.xlsx',mode="overwrite")

3 để xem kết quả file excel được load vào dataframe. Lưu ý từ hình bên dưới rằng dữ liệu được sắp xếp thành định dạng bảng giúp dễ dàng sử dụng để phân tích thêm

Mã PySpark sau đây cho biết cách đọc tệp CSV và tải tệp đó vào khung dữ liệu. Với phương pháp này, không cần phải tham khảo Thư viện Maven Spark Excel trong mã

csv=spark.read.format("csv").option("header", "true").option("inferSchema", "true").load("/mnt/raw/dimdates.csv")

Lệnh

csv.select("*").write.format('com.crealytics.spark.excel').option("header","true").option("inferSchema","true").save('/mnt/raw/dimdate.xlsx',mode="overwrite")

4 sau đó sẽ truy xuất kết quả ở định dạng bảng tương tự như ví dụ trước

Để đưa trường hợp sử dụng tiến thêm một bước, hãy lưu ý từ mã PySpark mẫu bên dưới rằng bạn có tùy chọn chọn nội dung từ tệp CSV và ghi nội dung đó vào tệp Excel với sự trợ giúp của thư viện Spark Excel Maven

csv.select("*").write.format('com.crealytics.spark.excel').option("header","true").option("inferSchema","true").save('/mnt/raw/dimdate.xlsx',mode="overwrite")

Chắc chắn rồi, sau khi mã hoàn thành chạy thành công, thông báo từ hình bên dưới có một ngày mờ mới. xlsx đã được tạo trong tài khoản ADLSgen2 của bạn

Để hiển thị thêm nội dung của tệp mới này, bạn có thể chạy mã PySpark sau để đọc tệp Excel vào khung dữ liệu

________số 8

Khi bạn chạy lệnh display(csv_to_xls), thông báo từ hình bên dưới rằng tệp Excel hiện chứa dữ liệu giống như tệp CSV

XlsxWriter là một mô-đun Python để viết văn bản, số, công thức và siêu liên kết tới nhiều trang tính trong Excel (. tệp xlsx). Nó hỗ trợ định dạng tùy biến cao và hơn thế nữa. Để cài đặt XlsxWriter, hãy chạy pip install xlsxwriter trong một ô của sổ ghi chép Python của bạn và để ý các thông báo sẽ được hiển thị, như thể hiện trong hình bên dưới, để xác nhận rằng nó đã được cài đặt thành công

Sau khi cài đặt, bạn sẽ có thể nhập xlsxwriter bằng cách sử dụng mã PySpark tương tự như sau. Sau đó, bạn có thể viết mã PySpark tùy chỉnh để trích xuất, chuyển đổi và tải dữ liệu trong tệp Excel của mình

import xlsxwriter
from shutil import copyfile
 
workbook = xlsxwriter.Workbook("/mnt/raw/Orders.xlsx")
worksheet = workbook.add_worksheet()
worksheet.write(0, 0, "Key")
worksheet.write(0, 1, "Value")
workbook.close()
 
copyfile('/mnt/raw/Orders.xlsx', '/mnt/raw/dimdate.xlsx')

XML

Khi làm việc với các tệp XML trong Databricks, bạn sẽ cần cài đặt thư viện Maven

csv.select("*").write.format('com.crealytics.spark.excel').option("header","true").option("inferSchema","true").save('/mnt/raw/dimdate.xlsx',mode="overwrite")

5 vào cụm, như thể hiện trong hình bên dưới. Tìm kiếm tia lửa. xml trong phần Tìm kiếm Trung tâm Maven. Sau khi cài đặt, mọi sổ ghi chép được gắn vào cụm sẽ có quyền truy cập vào thư viện đã cài đặt này

Sau khi bạn cài đặt thư viện XML, bạn cần đảm bảo rằng tệp dữ liệu xml của bạn được tải lên tài khoản ADLSgen2 được gắn vào không gian làm việc Databricks của bạn. Mã bên dưới hiển thị một số bản ghi mẫu của sách tệp XML. xml được sử dụng trong ví dụ này. Đây là tệp mẫu của Microsoft có thể được tìm thấy tại đây. https. // tài liệu. Microsoft. com/en-us/previous-versions/windows/desktop/ms762271(v=vs. 85). Ngoài ra, bạn có thể tham khảo file xml mẫu khác liên quan đến Đơn mua hàng tại đây. https. // tài liệu. Microsoft. com/en-us/dotnet/standard/linq/sample-xml-file-multiple-purchase-orders

csv.select("*").write.format('com.crealytics.spark.excel').option("header","true").option("inferSchema","true").save('/mnt/raw/dimdate.xlsx',mode="overwrite")

1

Sau khi tệp xml của bạn được tải vào tài khoản ADLSgen2 của bạn, hãy chạy tập lệnh PySpark sau được hiển thị trong hình bên dưới để đọc tệp xml vào khung dữ liệu và hiển thị kết quả. Lưu ý rằng định dạng không phải là dạng bảng, như mong đợi vì chúng tôi chưa tích hợp gói spark xml vào mã

Đây là mã PySpark mà bạn sẽ cần chạy để tạo lại kết quả như trong hình bên dưới

csv.select("*").write.format('com.crealytics.spark.excel').option("header","true").option("inferSchema","true").save('/mnt/raw/dimdate.xlsx',mode="overwrite")

2

Tiếp theo, hãy chạy mã PySpark sau để tải tệp xml của bạn vào khung dữ liệu bằng cách sử dụng gói spark xml maven đã cài đặt trước đó và hiển thị kết quả ở định dạng bảng, như trong hình bên dưới

Đây là mã PySpark mà bạn sẽ cần chạy để tạo lại kết quả như trong hình bên dưới. rowTag là thẻ hàng để coi là một hàng và rootTag là thẻ gốc để coi là gốc

csv.select("*").write.format('com.crealytics.spark.excel').option("header","true").option("inferSchema","true").save('/mnt/raw/dimdate.xlsx',mode="overwrite")

3

Với khối mã PySpark tiếp theo này, bạn sẽ có thể sử dụng gói spark xml để ghi kết quả của khung dữ liệu trở lại tệp xml có tên là sách mới. xml

csv.select("*").write.format('com.crealytics.spark.excel').option("header","true").option("inferSchema","true").save('/mnt/raw/dimdate.xlsx',mode="overwrite")

4

Sau đó, bạn có thể chạy đoạn mã sau để đọc tệp và truy xuất kết quả vào một khung dữ liệu

csv.select("*").write.format('com.crealytics.spark.excel').option("header","true").option("inferSchema","true").save('/mnt/raw/dimdate.xlsx',mode="overwrite")

5

Cuối cùng, bạn cũng có thể tạo bảng SQL bằng cú pháp sau chỉ định định dạng xml, đường dẫn tệp xml và rowTag. Với bảng này được tạo, bạn sẽ có thể viết các tập lệnh SQL để truy vấn dữ liệu xml của mình ở định dạng bảng

csv=spark.read.format("csv").option("header", "true").option("inferSchema", "true").load("/mnt/raw/dimdates.csv")

0

JSON

Có nhiều tình huống mà bạn có thể cần đọc và ghi dữ liệu JSON được lưu trữ trong tài khoản ADLSgen2 của mình từ sổ ghi chép Databricks. Mã sau đây

csv.select("*").write.format('com.crealytics.spark.excel').option("header","true").option("inferSchema","true").save('/mnt/raw/dimdate.xlsx',mode="overwrite")

6 xác định khung dữ liệu dựa trên việc đọc tệp json từ tài khoản ADLSgen2 được gắn kết của bạn. Khi lệnh display(json) được chạy trong một ô của sổ ghi chép của bạn, hãy chú ý từ hình bên dưới rằng kết quả được hiển thị ở định dạng bảng

Để tham khảo, đây là ba hàng đầu tiên của tệp Customer1 để hiển thị cấu trúc của định dạng json

csv=spark.read.format("csv").option("header", "true").option("inferSchema", "true").load("/mnt/raw/dimdates.csv")

1

Khối mã tiếp theo này là cú pháp SQL cũng có thể được chạy trong sổ ghi chép Python của bạn bằng cách chỉ định lệnh %sql ở đầu tập lệnh. Với các tập lệnh sau, bạn sẽ có thể tạo chế độ xem SQL tạm thời của dữ liệu định dạng json. Sau đó, bạn có thể viết các câu lệnh SQL để truy vấn dạng xem giống như bạn làm với bảng SQL thông thường để truy xuất kết quả ở định dạng bảng

csv=spark.read.format("csv").option("header", "true").option("inferSchema", "true").load("/mnt/raw/dimdates.csv")

2

Trong kịch bản tiếp theo, bạn có thể đọc dữ liệu json nhiều dòng bằng các lệnh PySpark đơn giản. Trước tiên, bạn cần tạo một tệp json chứa dữ liệu nhiều dòng, như được hiển thị trong mã bên dưới. Mã này sẽ tạo ra nhiều dòng. json trong tài khoản ADLSgen2 được gắn kết của bạn

csv=spark.read.format("csv").option("header", "true").option("inferSchema", "true").load("/mnt/raw/dimdates.csv")

3

Sau khi tệp được tạo, bạn có thể đọc tệp bằng cách chạy đoạn mã sau.

csv.select("*").write.format('com.crealytics.spark.excel').option("header","true").option("inferSchema","true").save('/mnt/raw/dimdate.xlsx',mode="overwrite")

7. Sau đó, lệnh

csv.select("*").write.format('com.crealytics.spark.excel').option("header","true").option("inferSchema","true").save('/mnt/raw/dimdate.xlsx',mode="overwrite")

8 sẽ truy xuất dữ liệu json nhiều dòng với khả năng mở rộng dữ liệu trong mỗi hàng, như thể hiện trong hình bên dưới

Hãy xem xét một kịch bản dựa trên JSON cuối cùng sẽ cho phép bạn tạo Tập dữ liệu phân tán đàn hồi (RDD), là tập hợp các phần tử được phân vùng trên các nút của cụm có thể hoạt động song song. Trong mã được hiển thị bên dưới, bạn sẽ lưu trữ đối tượng JSON trên mỗi chuỗi trong khung dữ liệu, tạo RDD bằng lệnh sc. lệnh song song hóa và cuối cùng bạn sẽ có thể đọc dữ liệu

csv=spark.read.format("csv").option("header", "true").option("inferSchema", "true").load("/mnt/raw/dimdates.csv")

4

Vì dữ liệu đã được hiển thị ở định dạng nhiều dòng, được hiển thị trong phần 1 của hình bên dưới, bạn có thể chạy lệnh sau.

csv.select("*").write.format('com.crealytics.spark.excel').option("header","true").option("inferSchema","true").save('/mnt/raw/dimdate.xlsx',mode="overwrite")

9 để chọn các trường muốn hiển thị dạng bảng, thể hiện ở mục 2 hình bên dưới. Ngoài ra, bạn có thể chạy lệnh này để hiển thị kết quả định dạng in như ở mục 3 hình bên dưới.

csv_to_xls=spark.read.format("com.crealytics.spark.excel").option("header", "true").option("inferSchema", "true").load("/mnt/raw/dimdate.xlsx")

0

ZIP

Khả năng đọc các tệp ZIP từ một URL và tải chúng xuống cục bộ trong sổ ghi chép Databricks của bạn và vào bộ chứa được gắn ADLSgen2 của bạn bằng cách nhập gói urllib vào mã sổ ghi chép của bạn. Chỉ cần chạy đoạn mã sau và chỉ định liên kết url tới dữ liệu zip của bạn. Cũng lưu ý rằng bạn sẽ không cần chạy lệnh cài đặt pip. Phần thứ hai của mã sẽ sử dụng lệnh ma thuật %sh để giải nén tệp zip. Khi bạn sử dụng %sh để thao tác trên các tệp, kết quả được lưu trữ trong thư mục /databricks/driver. Trước khi tải tệp bằng API Spark, bạn có thể di chuyển tệp sang DBFS bằng Databricks Utilities. Khối mã cuối cùng trong phần này của tập lệnh sẽ liệt kê các tệp được lưu trữ trong thư mục databricks/driver

csv=spark.read.format("csv").option("header", "true").option("inferSchema", "true").load("/mnt/raw/dimdates.csv")

5

Lưu ý từ hình bên dưới rằng nhiệt độ. tệp csv tồn tại trong /databricks/driver/

Cuối cùng, bạn có thể chạy tập lệnh sau để di chuyển tệp từ thư mục databricks/driver sang tài khoản ADLSgen2 được gắn của bạn. Phần thứ hai của mã sẽ tải tệp CSV đã giải nén vào một khung dữ liệu và hiển thị nó. Mã cuối cùng trong phần này hiển thị một tùy chọn để chạy lệnh ma thuật %sh để giải nén một. zip, khi cần

csv=spark.read.format("csv").option("header", "true").option("inferSchema", "true").load("/mnt/raw/dimdates.csv")

6

Hình bên dưới mô tả việc hiển thị kết quả dạng bảng của dữ liệu được giải nén đã được tải vào khung dữ liệu

Scala

Thông thường, các nhà phát triển có thể chọn viết mã của họ bằng Scala vì nó cũng hướng đối tượng như Python và nhanh hơn Python 10 lần do ngôn ngữ kiểu tĩnh của nó. Một số nhà phát triển có thể thấy sử dụng dễ dàng và thân thiện hơn Python. Ngoài ra, Scala xử lý đồng thời và song song rất tốt, trong khi Python không hỗ trợ đa luồng thực sự. Như đã nói, các nhà phát triển có thể chuyển đổi giữa các ngôn ngữ Scala, Python, SQL và R trong sổ ghi chép của họ bằng cách chỉ định ngôn ngữ bằng cách sử dụng lệnh ma thuật %scala. Nhiều ngôn ngữ có thể được kết hợp trong cùng một sổ ghi chép bằng cách sử dụng quy trình này. Ví dụ: đoạn mã sau sẽ đạt được các mục tiêu giống như tập lệnh PySpark trong phần XML, với một số khác biệt rõ ràng về cú pháp. Điều này cho thấy cả PySpark và Scala đều có thể đạt được kết quả như nhau

csv=spark.read.format("csv").option("header", "true").option("inferSchema", "true").load("/mnt/raw/dimdates.csv")

7

SQL

Thông qua một số tình huống trong bài viết này, chúng ta đã khám phá được việc viết mã SQL trong sổ ghi chép dễ dàng như thế nào. Điều này có thể đạt được bằng cách tạo sổ ghi chép mới với ngôn ngữ mặc định được đặt thành SQL hoặc bằng cách chỉ định lệnh %sql ma thuật trong các ô của sổ ghi chép. Trong sổ ghi chép, Databricks sử dụng phương ngữ SQL tiêu chuẩn ANSI theo mặc định, điều này giúp việc di chuyển khối lượng công việc SQL tại chỗ của bạn sang Lakehouse dễ dàng hơn nhiều. Hơn nữa, với SQL, bạn có thể truy vấn JSON cũng như tạo Hàm do người dùng xác định (UDF). Đoạn mã sau cho biết cách truy vấn dữ liệu định dạng json lồng nhau bằng SQL

csv=spark.read.format("csv").option("header", "true").option("inferSchema", "true").load("/mnt/raw/dimdates.csv")

8

Các UDF SQL rất dễ tạo dưới dạng các hàm tạm thời hoặc vĩnh viễn có thể được sử dụng lại qua các truy vấn và chúng cho phép các nhà phát triển mở rộng và tùy chỉnh mã SQL trong Databricks. Chúng đơn giản hóa quá trình tạo truy vấn thông qua khả năng đọc và tính mô đun tốt hơn. Đoạn mã dưới đây cho thấy một ví dụ về cách một hàm có thể được tạo để trả về số lượng sách tối đa được bán cho một id sách đã chỉ định

Chúng ta có thể đọc excel trong spark không?

PySpark là gì?