Como trabalhar com o BigQuery

O Google BigQuery é uma ferramenta versátil que resolve problemas de armazenamento e consulta de grandes conjuntos de dados. Com ele, você não precisa se preocupar com formatos de dados, recursos subjacentes e outros aspectos que não fazem parte da sua análise.

Antes de começar

Revise os seguintes recursos:

Conjunto de dados

Para usar o BigQuery em um projeto do Google Cloud, primeiro crie um Dataset usando o cmdlet New-BqDataset. Esse processo usa informações básicas e cria o recurso do lado do servidor. Um objeto de referência Dataset é retornado localmente.

# Makes a new dataset with DatasetId "page_views".
$dataset = New-BqDataset "page_views" -Name "Page Views" `
    -Description "Page views from 2014 onwards"

Para receber um objeto de referência para um conjunto de dados, use Get-BqDataset.

# Two ways to get a Dataset: by DatasetId and by Dataset object.
$dataset = Get-BqDataset "page_views"
$dataset = $dataset | Get-BqDataset

Este objeto $dataset pode ser modificado e transmitido para outros cmdlets como Set-BqDataset para manipular recursos de nuvem. Esse cmdlet também processa a adição e remoção de rótulos com -SetLabel e -ClearLabel.

# Updates the Name field of $dataset.
$dataset.Name = "PageView Data"
$dataset = Set-BqDataset "page_views"
# Adds the labels 'department' and 'purpose' to $dataset.
$dataset = Set-BqDataset "page_views" -SetLabel `
    @{"department" = "statistics"; "purpose" = "metrics"}

Os rótulos são usados para marcar conjuntos de dados com palavras-chave e/ou valores para posterior filtragem e pesquisa. O cmdlet Get-BqDataset tem uma sinalização -Filter integrada que permite um controle refinado na listagem de conjuntos de dados para processamento com outros cmdlets.

# Filter Datasets by the department label.
$stats = Get-BqDataset -Filter "department:statistics"

Datasets podem ser excluídos pelo cmdlet Remove-BqDataset. Esse cmdlet é compatível com ShouldProcess (o parâmetro -WhatIf) e solicitará a confirmação do usuário antes de excluir um Dataset que não esteja vazio. Essa proteção pode ser ignorada com o parâmetro -Force na criação do script.

# Deletes $dataset.
$dataset | Remove-BqDataset

Table

Cada Dataset tem um número de Tables para armazenar dados. Tables são criadas com o cmdlet New-BqTable por meio da transmissão de um TableId e do Dataset em que a tabela residirá. O Dataset pode ser transmitido por objeto ou com o parâmetro -DatasetId. Get-BqTable e Set-BqTable funcionam da mesma maneira que os cmdlets do conjunto de dados Get- e Set- acima.

# Creates a new table in the dataset from above.
$table = $dataset | New-BqTable "logs2014" `
    -Description "Log data from Jan 2014 to Dec 2014 inclusive"

# Gets a reference object for "page_views:logs2014".
$table = Get-BqTable "logs2014" -DatasetId "page_views"

# Modifies the Name attribute of logs2014.
$table.Name = "Logs 2014"
$table = $table | Set-BqTable

Tables podem ser excluídos pelo cmdlet Remove-BqTable. Este cmdlet é compatível com o ShouldProcess, no parâmetro -WhatIf, e solicitará confirmação do usuário antes de excluir uma Table que contenha dados. Essa proteção pode ser ignorada com o parâmetro -Force.

# Deletes $table.
$table | Remove-BqTable -Force

Schema

Tables precisa de Schemas para descrever o formato dos dados que eles contêm. Os esquemas são criados com os cmdlets New-BqSchema e Set-BqSchema. New-BqSchema pode aceitar os formatos das linhas como parâmetros diretamente ou como uma matriz JSON de descrições de linhas. Os resultados de New-BqSchema são sempre passados para Set-BqSchema, que podem gerar um objeto Schema ou atribuir o esquema a um Table atual.

# Assigns a Schema to $table
$table = Get-BqTable "logs2014" -DatasetId "page_views"
New-BqSchema "Page" "STRING" | New-BqSchema "Referrer" "STRING" |
    New-BqSchema "Timestamp" "DATETIME" | Set-BqSchema $table

# Creates a schema object to be used in multiple tables.
$schema = New-BqSchema "Page" "STRING" | New-BqSchema "Referrer" "STRING" |
    New-BqSchema "Timestamp" "DATETIME" | Set-BqSchema

Objetos de Schema podem ser transmitidos como parâmetros na criação de Table, se forem criados com antecedência.

# Creates a new table with the Schema object from above.
$table = $dataset | New-BqTable "logs2014" -Schema $schema

TableRow

Os dados são adicionados e removidos de Tables em Linhas. Essas linhas podem ser acessadas usando os cmdlets Add-BqTableRow e Get-BqTableRow. Add-BqTableRow aceita arquivos CSV, JSON e AVRO para importação no BigQuery.

# Ingests a CSV file and appends its rows onto the table 'my_dataset:my_table'.
$table = New-BqTable "logs2014" -DatasetId "page_views"
$table | Add-BqTableRow CSV $filename -SkipLeadingRows 1 `
                                      -WriteMode WriteAppend

# Returns a list of the rows in 'page_views:logs2014'.
$list = Get-BqTable "logs2014" -DatasetID "page_views" | Get-BqTableRow

Jobs

Há quatro tipos de Jobs: consulta, carregamento, extração e cópia. Os jobs de consulta executam consultas no estilo SQL e produzem resultados em tabelas.

# Query Job: starts a query and outputs results into $table.
Start-BqJob -Query "SELECT * FROM ``page_views:logs2014``" `
            -Destination $table

Os jobs de carregamento importam arquivos do Google Cloud Storage para o BigQuery.

# Load Job: adds TableRows to $table from the file specified
# on Cloud Storage.
$job = $dest_table | Start-BqJob `
    -Load CSV "gs://page_views/server_logs_raw_2014.csv"

Os jobs de extração exportam tabelas do BigQuery para o Google Cloud Storage.

# Extract Job: exports $src_table to a Cloud Storage file.
$job = $src_table | Start-BqJob `
    -Extract CSV "gs://page_views/logs2014.csv"

Os jobs de cópia copiam uma tabela atual para outra tabela nova ou existente.

# Copy Job: Starts a copy job, cancels it, and polls until the job is completely done.
$job = $table | Start-BqJob -Copy $dest_table
$result = $job | Stop-BqJob
while ($result.Status.State -ne "DONE") {
    $result = $result | Get-BqJob
}

Start-BqJob inicia qualquer um desses tipos de jobs como uma operação assíncrona. Use a sinalização -PollUntilComplete para ter o bloco cmdlet até que o job seja concluído. Receive-BqJob retornará os resultados de um job de consulta assim que for concluído. Get-BqJob retornará um objeto de referência que detalha o estado atual e as estatísticas no job. Stop-BqJob enviará uma solicitação ao servidor para interromper um determinado job e depois retornará imediatamente.

Observação sobre a formatação dos nomes de tabelas dentro das strings de consulta: o formato do BigQuery determina que os nomes das tabelas sejam cercados por acentos graves, mas esses acentos também são operadores de escape do PowerShell. Por isso, eles precisam ser escapados, com a adição de um segundo acento grave. Consulte um exemplo no código de amostra dos jobs de Consulta.