Trong lớp học lập trình này, bạn sẽ tìm hiểu cách sử dụng một số tính năng nâng cao của BigQuery, bao gồm:
- Hàm do người dùng xác định trong JavaScript
- Bảng phân vùng
- Truy vấn trực tiếp dựa trên dữ liệu sống trong Google Cloud Storage và Google Drive.
Bạn sẽ lấy dữ liệu từ Ủy ban bầu cử liên bang Hoa Kỳ, dọn dẹp và tải dữ liệu đó vào BigQuery. Bạn cũng sẽ có cơ hội đặt một số câu hỏi thú vị trong tập dữ liệu đó.
Trong khi lớp học lập trình này không có trải nghiệm trước đó với BigQuery, một số hiểu biết về SQL sẽ giúp bạn tận dụng tối đa tính năng này.
Kiến thức bạn sẽ học được
- Cách sử dụng các hàm do người dùng xác định bằng JavaScript để thực hiện các thao tác khó thực hiện trong SQL.
- Cách sử dụng BigQuery để thực hiện các thao tác ETL (Trích xuất, Chuyển đổi, Tải) trên dữ liệu nằm trong các cửa hàng dữ liệu khác, như Google Cloud Storage và Google Drive.
Bạn cần có
- Một dự án Google Cloud đã bật tính năng thanh toán.
- Bộ chứa Google Cloud Storage
- Đã cài đặt Google Cloud SDK
Bạn sẽ sử dụng hướng dẫn này như thế nào?
Bạn đánh giá mức độ trải nghiệm của bạn với BigQuery như thế nào?
Thiết lập môi trường theo tiến độ riêng
Nếu chưa có Tài khoản Google (Gmail hoặc Google Apps), thì bạn phải tạo một tài khoản. Đăng nhập vào bảng điều khiển của Google Cloud Platform (console.cloud.google.com) và tạo một dự án mới:
Hãy ghi nhớ mã dự án, một tên duy nhất trên tất cả các dự án Google Cloud (tên ở trên đã được sử dụng và sẽ không hoạt động cho bạn!). Lớp học này sẽ được gọi sau này trong lớp học lập trình này là PROJECT_ID.
Tiếp theo, bạn sẽ cần bật tính năng thanh toán trong Cloud Console để sử dụng tài nguyên của Google Cloud.
Nếu tham gia lớp học lập trình này, bạn sẽ không mất quá vài đô la, nhưng có thể sẽ hiệu quả hơn nếu bạn quyết định sử dụng nhiều tài nguyên hơn hoặc nếu bạn để các tài nguyên đó hoạt động (xem "cleanup" ở cuối tài liệu này).
Người dùng mới của Google Cloud Platform đủ điều kiện dùng thử 300 đô la dùng thử miễn phí.
Google Cloud Shell
Mặc dù Google Cloud và Big Query có thể hoạt động từ xa trên máy tính xách tay, nhưng trong lớp học lập trình này, chúng ta sẽ dùng Google Cloud Shell, một môi trường dòng lệnh chạy trong Cloud.
Máy ảo dựa trên Debian này được tải bằng tất cả công cụ phát triển mà bạn cần. cung cấp một thư mục gốc 5GB cố định và chạy trên Google Cloud, qua đó nâng cao đáng kể hiệu suất và khả năng xác thực mạng. Điều này có nghĩa là tất cả những gì bạn cần cho lớp học lập trình này là trình duyệt (có, nó hoạt động trên Chromebook).
Để kích hoạt Google Cloud Shell, từ bảng điều khiển dành cho nhà phát triển, bạn chỉ cần nhấp vào nút ở phía trên cùng bên phải (chỉ mất vài phút để cấp phép và kết nối với môi trường):
Nhấp vào nút "Start Cloud Shell":
Sau khi kết nối với shell Cloud, bạn sẽ thấy rằng bạn đã được xác thực và dự án này đã được đặt thành PROJECT_ID của bạn:
gcloud auth list
Đầu ra lệnh
Credentialed accounts: - <myaccount>@<mydomain>.com (active)
gcloud config list project
Đầu ra lệnh
[core] project = <PROJECT_ID>
Cloud Shell cũng đặt một số biến môi trường theo mặc định. Những biến này có thể hữu ích khi bạn chạy các lệnh trong tương lai.
echo $GOOGLE_CLOUD_PROJECT
Đầu ra lệnh
<PROJECT_ID>
Nếu vì lý do nào đó mà dự án chưa được đặt, bạn chỉ cần đưa ra lệnh sau :
gcloud config set project <PROJECT_ID>
Bạn đang tìm kiếm PROJECT_ID của mình? Kiểm tra mã nhận dạng mà bạn đã sử dụng trong các bước thiết lập hoặc tìm kiếm mã đó trong trang tổng quan bảng điều khiển:
LƯU Ý QUAN TRỌNG: Cuối cùng, hãy đặt cấu hình dự án và vùng mặc định:
gcloud config set compute/zone us-central1-f
Bạn có thể chọn nhiều khu vực khác nhau. Tìm hiểu thêm trong Tài liệu về khu vực và amp; khu vực.
Để chạy truy vấn BigQuery trong lớp học lập trình này, bạn cần có tập dữ liệu của riêng mình. Chọn tên cho mục đó, chẳng hạn như campaign_funding. Chạy các lệnh sau trong shell (ví dụ: CloudShell):
$ DATASET=campaign_funding
$ bq mk -d ${DATASET}
Dataset 'bq-campaign:campaign_funding' successfully created.
Sau khi tạo tập dữ liệu của mình, bạn đã sẵn sàng hoạt động. Việc chạy lệnh này cũng sẽ giúp xác minh rằng bạn đã thiết lập chính xác ứng dụng dòng lệnh bq, quá trình xác thực đang hoạt động và bạn có quyền ghi đối với dự án trên đám mây mà bạn đang hoạt động. Nếu có nhiều dự án, bạn sẽ được nhắc chọn một dự án mà bạn quan tâm trong danh sách.
Tập dữ liệu tài chính của Ủy ban bầu cử liên bang Hoa Kỳ đã được giải nén và sao chép vào bộ chứa GCS gs://campaign-funding/.
Hãy tải một trong các tệp nguồn xuống cục bộ để chúng tôi có thể hình dung được tệp đó. Chạy các lệnh sau từ cửa sổ lệnh:
$ gsutil cp gs://campaign-funding/indiv16.txt . $ tail indiv16.txt
Thao tác này sẽ hiển thị nội dung của tệp đóng góp riêng lẻ. Chúng tôi sẽ xem xét ba loại tệp mà chúng tôi hướng đến:
Chúng tôi sẽ không tải trực tiếp dữ liệu thô vào BigQuery; thay vào đó, chúng tôi sẽ truy vấn dữ liệu từ Google Cloud Storage. Để làm như vậy, chúng tôi cần biết giản đồ và một số thông tin về giản đồ đó.
Tập dữ liệu được mô tả trên trang web bầu cử liên bang tại đây. Giản đồ cho các bảng mà chúng ta sẽ xem xét là:
Để liên kết đến các bảng, chúng ta cần tạo một định nghĩa bảng cho các giản đồ đó. Chạy các lệnh sau để tạo định nghĩa bảng riêng lẻ:
$ bq mkdef --source_format=CSV \
gs://campaign-funding/indiv*.txt \
"CMTE_ID, AMNDT_IND, RPT_TP, TRANSACTION_PGI, IMAGE_NUM, TRANSACTION_TP, ENTITY_TP, NAME, CITY, STATE, ZIP_CODE, EMPLOYER, OCCUPATION, TRANSACTION_DT, TRANSACTION_AMT:FLOAT, OTHER_ID, TRAN_ID, FILE_NUM, MEMO_CD, MEMO_TEXT, SUB_ID" \
> indiv_def.json
Mở tệp indiv_dev.json bằng trình chỉnh sửa văn bản mà bạn yêu thích và xem nội dung. Tệp này có chứa tệp json mô tả cách diễn giải tệp dữ liệu FEC.
Chúng tôi sẽ cần thực hiện hai chỉnh sửa nhỏ đối với phần csvOptions. Thêm giá trị fieldDelimiter là "|" và giá trị quote là "" (chuỗi trống). Đây là cần thiết vì tệp dữ liệu không thực sự được phân tách bằng dấu phẩy, mà được phân tách bằng dấu sổ đứng:
$ sed -i 's/"fieldDelimiter": ","/"fieldDelimiter": "|"/g; s/"quote": "\\""/"quote":""/g' indiv_def.json
Tệp indiv_dev.json hiện sẽ có dạng :
"fieldDelimiter": "|",
"quote":"",
Vì việc tạo định nghĩa bảng cho bảng ủy ban và bảng ứng cử viên là tương tự nhau và giản đồ chứa một chút mẫu, nên hãy tải xuống những tệp đó.
$ gsutil cp gs://campaign-funding/candidate_def.json . Copying gs://campaign-funding/candidate_def.json... / [1 files][ 945.0 B/ 945.0 B] Operation completed over 1 objects/945.0 B. $ gsutil cp gs://campaign-funding/committee_def.json . Copying gs://campaign-funding/committee_def.json... / [1 files][ 949.0 B/ 949.0 B] Operation completed over 1 objects/949.0 B.
Những tệp này sẽ có dạng giống như tệp indiv_dev.json. Xin lưu ý rằng bạn cũng có thể tải tệp indiv_def.json xuống, phòng trường hợp bạn gặp khó khăn trong việc nhận đúng giá trị.
Tiếp theo, hãy liên kết bảng BigQuery với những tệp này. Chạy các lệnh sau:
$ bq mk --external_table_definition=indiv_def.json -t ${DATASET}.transactions
Table 'bq-campaign:campaign_funding.transactions' successfully created.
$ bq mk --external_table_definition=committee_def.json -t ${DATASET}.committees
Table 'bq-campaign:campaign_funding.committees' successfully created.
$ bq mk --external_table_definition=candidate_def.json -t ${DATASET}.candidates
Table 'bq-campaign:campaign_funding.candidates' successfully created.
Thao tác này sẽ tạo 3 bảng bigquery: giao dịch, ủy ban và ứng viên. Bạn có thể truy vấn các bảng này giống như các bảng BigQuery thông thường, nhưng các bảng đó không thực sự được lưu trữ trong BigQuery, mà chúng sẽ được lưu trữ trong Google Cloud Storage. Nếu bạn cập nhật các tệp cơ bản, thì các bản cập nhật sẽ ngay lập tức được phản ánh trong các truy vấn mà bạn chạy.
Tiếp theo, hãy thử chạy một số truy vấn. Mở Giao diện người dùng Web BigQuery.
Tìm tập dữ liệu của bạn trong ngăn điều hướng bên trái (bạn có thể phải thay đổi trình đơn thả xuống dự án ở góc trên cùng bên trái), nhấp vào nút "TRUY CẬP VÀO QUERY 39%" màu đỏ lớn và nhập truy vấn sau vào hộp:
SELECT * FROM [campaign_funding.transactions] WHERE EMPLOYER contains "GOOGLE" ORDER BY TRANSACTION_DT DESC LIMIT 100
Thao tác này sẽ tìm thấy 100 khoản quyên góp gần đây nhất trong chiến dịch của nhân viên Google. Nếu bạn muốn, hãy thử chơi xung quanh và tìm khoản quyên góp chiến dịch từ cư dân trên mã bưu chính của bạn hoặc tìm khoản quyên góp lớn nhất trong thành phố của bạn.
Truy vấn và kết quả sẽ trông như sau:
Tuy nhiên, bạn có thể nhận thấy rằng bạn không thể thực sự biết được người nhận là những khoản quyên góp nào. Chúng tôi cần nghĩ ra một số truy vấn huyền ảo để có được thông tin đó.
Nhấp vào bảng giao dịch trong ngăn bên trái, rồi nhấp vào thẻ giản đồ. Ảnh phải trông như ảnh chụp màn hình dưới đây:
Chúng ta có thể thấy một danh sách các trường khớp với định nghĩa bảng mà chúng ta đã chỉ định trước đó. Bạn có thể nhận thấy không có trường nào người nhận hoặc bất kỳ cách nào để tìm ra đề xuất quyên góp được hỗ trợ. Tuy nhiên, có một trường có tên là CMTE_ID. Thao tác này sẽ cho phép chúng tôi liên kết ủy ban mà người nhận sẽ nhận được khoản quyên góp đó. Thông tin này vẫn không hữu ích nhiều.
Tiếp theo, hãy nhấp vào bảng ủy ban để xem giản đồ của bảng. Chúng tôi có CMET_ID để có thể tham gia bảng giao dịch. Một trường khác là CAND_ID; bạn có thể kết hợp trường này với bảng CAND_ID trong bảng ứng viên. Cuối cùng, chúng tôi có mối liên kết giữa các giao dịch và ứng viên bằng cách xem qua bảng ủy ban.
Lưu ý rằng không có thẻ xem trước cho bảng dựa trên GCS. Điều này là do để đọc dữ liệu, BigQuery cần phải đọc từ một nguồn dữ liệu bên ngoài. Hãy lấy một mẫu dữ liệu bằng cách chạy một truy vấn đơn giản là "SELECT *\39; trên bảng ứng viên.
SELECT * FROM [campaign_funding.candidates] LIMIT 100
Kết quả sẽ trông như sau:
Bạn có thể nhận thấy rằng các tên ứng viên là ALL CAPS và được trình bày bằng "lastname, firstname" don. Điều này hơi khó chịu vì đây không thực sự là cách chúng ta nghĩ về các ứng cử viên; chúng ta thích xem "Barack Obama" hơn "OBAMA, BarACK". Hơn nữa, ngày giao dịch (TRANSACTION_DT) trong bảng giao dịch cũng hơi phức tạp. Chúng là các giá trị chuỗi ở định dạng YYYYMMDD. Chúng tôi sẽ giải quyết những vấn đề này trong phần tiếp theo.
Bây giờ, chúng ta đã hiểu cách giao dịch có liên quan đến ứng viên, hãy chạy một truy vấn để tìm hiểu xem ai đang trao tiền cho ai. Cắt và dán truy vấn sau vào hộp soạn tin:
SELECT affiliation, SUM(amount) AS amount
FROM (
SELECT *
FROM (
SELECT
t.amt AS amount,
t.occupation AS occupation,
c.affiliation AS affiliation,
FROM (
SELECT
trans.TRANSACTION_AMT AS amt,
trans.OCCUPATION AS occupation,
cmte.CAND_ID AS CAND_ID
FROM [campaign_funding.transactions] trans
RIGHT OUTER JOIN EACH (
SELECT
CMTE_ID,
FIRST(CAND_ID) AS CAND_ID
FROM [campaign_funding.committees]
GROUP EACH BY CMTE_ID ) cmte
ON trans.CMTE_ID = cmte.CMTE_ID) AS t
RIGHT OUTER JOIN EACH (
SELECT
CAND_ID,
FIRST(CAND_PTY_AFFILIATION) AS affiliation,
FROM [campaign_funding.candidates]
GROUP EACH BY CAND_ID) c
ON t.CAND_ID = c.CAND_ID )
WHERE occupation CONTAINS "ENGINEER")
GROUP BY affiliation
ORDER BY amount DESC
Truy vấn này kết hợp với bảng giao dịch sẽ vào bảng ủy ban rồi đến bảng ứng viên. Hệ thống này chỉ xem xét các giao dịch từ những người có từ "ENGINEER" trong chức danh nghề nghiệp của họ. Truy vấn tổng hợp kết quả theo liên kết đảng; điều này cho phép chúng tôi thấy được sự phân bố cho nhiều đảng phái chính trị khác nhau trong số các kỹ sư.
Chúng ta có thể thấy rằng các kỹ sư là một nhóm khá cân bằng, mang lại ít hay nhiều sự khác biệt cho các đảng phái dân chủ và cộng hòa. Nhưng bữa tiệc "DFL#39; là gì? Việc sử dụng tên đầy đủ thay vì chỉ có một mã gồm ba chữ cái sẽ rất tốt?
Mã bên được xác định trên trang web của FEC. Có một bảng khớp với mã đảng với tên đầy đủ (thực ra là "DFL#39; 'Demo-Farmer-Labor×39"). Mặc dù chúng tôi có thể thực hiện bản dịch theo cách thủ công trong truy vấn của mình, nhưng điều đó có vẻ khó khăn và đồng bộ hóa.
Điều gì xảy ra nếu chúng ta có thể phân tích cú pháp HTML như một phần của truy vấn? Hãy nhấp chuột phải vào một vị trí bất kỳ trên trang đó và xem "xem nguồn của trang" Có rất nhiều thông tin tiêu đề / tạo sẵn trong nguồn, nhưng hãy tìm thẻ <table>. Mỗi hàng ánh xạ nằm trong một phần tử HTML <tr>, tên và mã đều được gói trong các phần tử <td>. Mỗi hàng sẽ có dạng như sau:
HTML có dạng như sau:
<tr bgcolor="#F5F0FF">
<td scope="row"><div align="left">ACE</div></td>
<td scope="row">Ace Party</td>
<td scope="row"></td>
</tr>
Hãy lưu ý rằng BigQuery không thể đọc tệp trực tiếp từ web; điều này là do bigquery có thể truy cập một nguồn từ hàng nghìn nhân viên cùng lúc. Nếu nội dung này được phép chạy trên các trang web ngẫu nhiên, thì về cơ bản, nó sẽ là một cuộc tấn công từ chối dịch vụ (viết tắt là MIME) được phân phối. Tệp html từ trang web FEC được lưu trữ trong bộ chứa gs://campaign-funding.
Chúng tôi sẽ tạo một bảng dựa trên dữ liệu tài trợ của chiến dịch. Bảng này tương tự như các bảng khác do GCS hỗ trợ. Sự khác biệt ở đây là chúng ta không thực sự có giản đồ; chúng ta sẽ chỉ sử dụng một trường trên mỗi hàng và gọi nó là "data#39;". Chúng ta sẽ giả sử rằng đó là tệp CSV, nhưng thay vì phân cách bằng dấu phẩy, chúng ta sẽ sử dụng dấu phân cách không có giá trị (`) và không có ký tự trích dẫn.
Để tạo bảng tra cứu bên, hãy chạy các lệnh sau từ dòng lệnh:
$ echo '{"csvOptions": {"allowJaggedRows": false, "skipLeadingRows": 0, "quote": "", "encoding": "UTF-8", "fieldDelimiter": "`", "allowQuotedNewlines": false}, "ignoreUnknownValues": true, "sourceFormat": "CSV", "sourceUris": ["gs://campaign-funding/party_codes.shtml"], "schema": {"fields": [{"type": "STRING", "name": "data"}]}}' > party_raw_def.json
$ bq mk --external_table_definition=party_raw_def.json \
-t ${DATASET}.raw_party_codes
Table 'bq-campaign:campaign_funding.raw_party_codes' successfully created.
Bây giờ, chúng ta sẽ sử dụng JavaScript để phân tích cú pháp tệp. Ở trên cùng bên phải của Trình chỉnh sửa truy vấn BigQuery phải là nút có nhãn "UDF Editor". Nhấp vào nút đó để chuyển sang chỉnh sửa JavaScript UDF. Trình chỉnh sửa UDF sẽ được điền một số mẫu nhận xét.
Hãy tiếp tục và xóa mã chứa mã đó rồi nhập mã sau:
function tableParserFun(row, emitFn) {
if (row.data != null && row.data.match(/<tr.*<\/tr>/) !== null) {
var txt = row.data
var re = />\s*(\w[^\t<]*)\t*<.*>\s*(\w[^\t<]*)\t*</;
matches = txt.match(re);
if (matches !== null && matches.length > 2) {
var result = {code: matches[1], name: matches[2]};
emitFn(result);
} else {
var result = { code: 'ERROR', name: matches};
emitFn(result);
}
}
}
bigquery.defineFunction(
'tableParser', // Name of the function exported to SQL
['data'], // Names of input columns
[{'name': 'code', 'type': 'string'}, // Output schema
{'name': 'name', 'type': 'string'}],
tableParserFun // Reference to JavaScript UDF
);
Javascript ở đây được chia thành hai phần; phần đầu tiên là hàm lấy hàng của đầu vào phát ra kết quả đã phân tích cú pháp. Định nghĩa còn lại là một định nghĩa đăng ký hoạt động như một Hàm do người dùng xác định (UDF) với tên tableParser và cho biết rằng hàm này nhận một cột đầu vào có tên "data#39; và xuất ra hai cột, mã và tên. Cột mã sẽ là mã gồm ba chữ cái, cột tên là tên đầy đủ của bên đó.
Chuyển trở lại thẻ "Query Editor) và nhập truy vấn sau:
SELECT code, name FROM tableParser([campaign_funding.raw_party_codes]) ORDER BY code
Việc chạy truy vấn này sẽ phân tích cú pháp tệp HTML thô và xuất giá trị của trường ở định dạng có cấu trúc. Trông khá đấy? Xem liệu bạn có thể tìm ra "DFL\39; là viết tắt của từ nào không.
Bây giờ, chúng ta có thể dịch mã nhóm sang tên, hãy thử dùng một truy vấn khác dùng mã này để tìm ra nội dung thú vị. Chạy truy vấn sau:
SELECT
candidate,
election_year,
FIRST(candidate_affiliation) AS affiliation,
SUM(amount) AS amount
FROM (
SELECT
CONCAT(REGEXP_EXTRACT(c.candidate_name,r'\w+,[ ]+([\w ]+)'), ' ',
REGEXP_EXTRACT(c.candidate_name,r'(\w+),')) AS candidate,
pty.candidate_affiliation_name AS candidate_affiliation,
c.election_year AS election_year,
t.amt AS amount,
FROM (
SELECT
trans.TRANSACTION_AMT AS amt,
cmte.committee_candidate_id AS committee_candidate_id
FROM [campaign_funding.transactions] trans
RIGHT OUTER JOIN EACH (
SELECT
CMTE_ID,
FIRST(CAND_ID) AS committee_candidate_id
FROM [campaign_funding.committees]
GROUP BY CMTE_ID ) cmte
ON trans.CMTE_ID = cmte.CMTE_ID) AS t
RIGHT OUTER JOIN EACH (
SELECT
CAND_ID AS candidate_id,
FIRST(CAND_NAME) AS candidate_name,
FIRST(CAND_PTY_AFFILIATION) AS affiliation,
FIRST(CAND_ELECTION_YR) AS election_year,
FROM [campaign_funding.candidates]
GROUP BY candidate_id) c
ON t.committee_candidate_id = c.candidate_id
JOIN (
SELECT
code,
name AS candidate_affiliation_name
FROM (tableParser([campaign_funding.raw_party_codes]))) pty
ON pty.code = c.affiliation )
GROUP BY candidate, election_year
ORDER BY amount DESC
LIMIT 100
Truy vấn này sẽ cho biết những ứng cử viên nào nhận được nhiều khoản quyên góp nhất trong chiến dịch và sẽ nêu rõ những liên kết đảng của họ.
Những bảng này không lớn và mất khoảng 30 giây để truy vấn. Nếu bạn thực hiện nhiều công việc với bảng, thì có thể bạn muốn nhập các bảng đó vào BigQuery. Bạn có thể chạy truy vấn ETL dựa trên bảng để buộc dữ liệu thành nội dung dễ sử dụng, sau đó lưu dưới dạng bảng vĩnh viễn. Điều này có nghĩa là bạn không phải luôn nhớ cách dịch mã bên và bạn cũng có thể lọc dữ liệu không chính xác trong khi bạn đang thực hiện việc đó.
Nhấp vào nút "Hiển thị các tùy chọn" rồi nhấp vào nút "chọn bảng#39; bên cạnh &&tt;Destination Table" nhãn. Chọn tập dữ liệu campaign_funding và nhập mã bảng dưới dạng "summarymsgid39; và chọn hộp đánh dấu "allow large results×39.
Bây giờ, hãy chạy truy vấn sau:
SELECT
CONCAT(REGEXP_EXTRACT(c.candidate_name,r'\w+,[ ]+([\w ]+)'), ' ', REGEXP_EXTRACT(c.candidate_name,r'(\w+),'))
AS candidate,
pty.candidate_affiliation_name as candidate_affiliation,
INTEGER(c.election_year) as election_year,
c.candidate_state as candidate_state,
c.office as candidate_office,
t.name as name,
t.city as city,
t.amt as amount,
c.district as candidate_district,
c.ici as candidate_ici,
c.status as candidate_status,
t.memo as memo,
t.state as state,
LEFT(t.zip_code, 5) as zip_code,
t.employer as employer,
t.occupation as occupation,
USEC_TO_TIMESTAMP(PARSE_UTC_USEC(
CONCAT(RIGHT(t.transaction_date, 4), "-",
LEFT(t.transaction_date,2), "-",
RIGHT(LEFT(t.transaction_date,4), 2),
" 00:00:00"))) as transaction_date,
t.committee_name as committee_name,
t.committe_designation as committee_designation,
t.committee_type as committee_type,
pty_cmte.committee_affiliation_name as committee_affiliation,
t.committee_org_type as committee_organization_type,
t.committee_connected_org_name as committee_organization_name,
t.entity_type as entity_type,
FROM (
SELECT
trans.ENTITY_TP as entity_type,
trans.NAME as name,
trans.CITY as city,
trans.STATE as state,
trans.ZIP_CODE as zip_code,
trans.EMPLOYER as employer,
trans.OCCUPATION as occupation,
trans.TRANSACTION_DT as transaction_date,
trans.TRANSACTION_AMT as amt,
trans.MEMO_TEXT as memo,
cmte.committee_name as committee_name,
cmte.committe_designation as committe_designation,
cmte.committee_type as committee_type,
cmte.committee_affiliation as committee_affiliation,
cmte.committee_org_type as committee_org_type,
cmte.committee_connected_org_name as committee_connected_org_name,
cmte.committee_candidate_id as committee_candidate_id
FROM [campaign_funding.transactions] trans
RIGHT OUTER JOIN EACH (
SELECT
CMTE_ID,
FIRST(CMTE_NM) as committee_name,
FIRST(CMTE_DSGN) as committe_designation,
FIRST(CMTE_TP) as committee_type,
FIRST(CMTE_PTY_AFFILIATION) as committee_affiliation,
FIRST(ORG_TP) as committee_org_type,
FIRST(CONNECTED_ORG_NM) as committee_connected_org_name,
FIRST(CAND_ID) as committee_candidate_id
FROM [campaign_funding.committees]
GROUP BY CMTE_ID
) cmte
ON trans.CMTE_ID = cmte.CMTE_ID) as t
RIGHT OUTER JOIN EACH
(SELECT CAND_ID as candidate_id,
FIRST(CAND_NAME) as candidate_name,
FIRST(CAND_PTY_AFFILIATION) as affiliation,
INTEGER(FIRST(CAND_ELECTION_YR)) as election_year,
FIRST(CAND_OFFICE_ST) as candidate_state,
FIRST(CAND_OFFICE) as office,
FIRST(CAND_OFFICE_DISTRICT) as district,
FIRST(CAND_ICI) as ici,
FIRST(CAND_STATUS) as status,
FROM [campaign_funding.candidates]
GROUP BY candidate_id) c
ON t.committee_candidate_id = c.candidate_id
JOIN (
SELECT code, name as candidate_affiliation_name
FROM (tableParser([campaign_funding.raw_party_codes]))) pty
ON pty.code = c.affiliation
JOIN (
SELECT code, name as committee_affiliation_name
FROM (tableParser([campaign_funding.raw_party_codes]))) pty_cmte
ON pty_cmte.code = t.committee_affiliation
WHERE t.amt > 0.0 and REGEXP_MATCH(t.state, "^[A-Z]{2}$") and t.amt < 1000000.0
Truy vấn này dài hơn đáng kể và có một số tùy chọn dọn dẹp bổ sung. Ví dụ: ngân sách này bỏ qua mọi thứ có số tiền lớn hơn 1 triệu đô la. Ứng dụng này cũng sử dụng biểu thức chính quy để biến "LASTNAME, FIRSTNAME" thành "FIRSTNAME LASTNAME". Nếu bạn cảm thấy mạo hiểm, hãy thử viết một UDF để làm tốt hơn nữa và sửa lỗi viết hoa (ví dụ: ");Firstname Lastname"
Cuối cùng, hãy thử chạy một số truy vấn dựa trên bảng campaign_funding.summary của bạn để xác minh rằng các truy vấn dựa vào bảng đó nhanh hơn. Trước tiên, đừng quên xóa tùy chọn truy vấn bảng đích, nếu không bạn có thể ghi đè bảng tóm tắt của mình!
Bây giờ, bạn đã làm sạch và nhập dữ liệu từ trang web của FEC vào BigQuery!
Những điều chúng tôi đã đề cập
- Sử dụng bảng được GCS hỗ trợ trong BigQuery.
- Sử dụng các hàm do người dùng xác định trong BigQuery.
Bước tiếp theo
- Hãy thử thực hiện một số truy vấn thú vị để biết ai đang chi trả cho ai trong chu kỳ bầu cử này.
Tìm hiểu thêm
- Tìm hiểu thêm về những việc bạn có thể làm với hàm do người dùng xác định.
- Đọc về nguồn dữ liệu liên kết (bao gồm cả GCS).
- Đăng câu hỏi và tìm câu trả lời trên Stack Overflow trong thẻ google-bigquery.
Hãy cho chúng tôi biết ý kiến của bạn
- Vui lòng dùng đường liên kết ở dưới cùng bên trái trang này để gửi vấn đề hoặc chia sẻ ý kiến phản hồi!