Como criar uma tabela externa de um arquivo Json no BigQuery por linha de comando


 Nesta postagem, mostraremos como usar criar uma tabela externa do BigQuery a partir de um arquivo json que esta armazenado no Cloud Storage.

O que é Cloud Storage?

Fornece, a nível mundial, um armazenamento de objetos altamente durável que escalona para exabytes de dados. É possível acessar dados instantaneamente de qualquer classe de armazenamento, integrar o armazenamento aos seus aplicativos com uma API exclusiva unificada e otimizar o preço e desempenho de forma fácil, neste tutorial será usado para armazenamento de dados do Hive e origem de leitura da tabela externa do BigQuery.

O que é BigQuery?

É um data warehouse totalmente gerenciado que permite realizar análises em um grande conjunto de dados, no patamar de petabytes.

Suporta consultas no formato ANSI SQL e criação de modelos de Machine Learning (ML) em uma sintaxe semelhante ao SQL.

O que são tabelas externas no BigQuery?

Em uma tabela externa os dados não estarão armazenados no BigQuery, mas podem ser consultados por ele.

Nessa abordagem somente os metadados são criados nele, ao consultar a tabela a fonte de dados externa que será consultada.

Atualmente, o BQ aceita essas fontes externas:

  • Bigtable
  • Cloud Storage
  • Google Drive
  • Cloud SQL (Beta)

Nos seguintes formatos:

  • Avro
  • CSV
  • JSON
  • ORC
  • Parquet

As “external tables” podem ser usadas principalmente quando os dados da origem são atualizados com frequência e para limpeza antes de armazenar o dado no BigQuery.

Passo a Passo — Linha de comando

1. O primeiro passo a fazer é o login em sua conta do GCP, para este tutorial você vai precisar de uma conta que possua um projeto.

2. Vá até a barra superior e clique no ícone do Cloud Shell, igual a imagem abaixo.

3. Agora temos que preparar as variáveis de ambiente que vamos usar nos outros passos:

a) Para definir a variável de região usaremos o código abaixo:

export REGION=us-central1

b) Para definir a variável de Zona usaremos o código abaixo:

export ZONE=us-central1-a

4. Nesse passo vamos definir o projeto a ser usado dentro do cloud shell, no campo [PROJECT_ID], insira o id do seu projeto:

gcloud config set project [PROJECT_ID]

5. Neste passo vamos definir a Zona a ser usada dentro do cloud shell com base na variável que definimos antes:

gcloud config set compute/zone $ZONE

6. Nesse passo vamos definir a Região a ser usada dentro do cloud shell com base na variável que definimos antes:

gcloud config set compute/region $REGION

7. Criaremos o bucket do Cloud Storage em que iremos exportar nossa tabela:

gsutil mb -l $REGION gs://[PROJECT_ID]-arquivos

8. Crie um arquivo chamado pessoas.json com o seguinte conteudo e coloque no seu bucket:

{"nome":"Joao", "email":"Joao@gmail.com", "idade":"23"}
{"nome":"Maria", "email":"Maria23@gmail.com", "idade":"28"}
{"nome":"Ana", "email":"Ana@gmail.com", "idade":"33"}
{"nome":"Rosana", "email":"Rosana32@gmail.com", "idade":"41"}

9. Agora a partir dos arquivos desse diretório, vamos criar nossa tabela externa no BQ. Crie um novo conjunto de dados:

bq mk dataset_teste

10. Crie uma definição de tabela usando como base os arquivos Json que estão dentro do nosso Storage:

bq mkdef \
--autodetect \
--source_format=NEWLINE_DELIMITED_JSON \
"gs://id_do_meu_projeto_meu_bucket/*.json" > /tmp/file_name.json

Observação:

Um arquivo de definição de tabela contém a definição de esquema de uma tabela externa e metadados, como o formato de dados da tabela e propriedades relacionadas.

Nesse exemplo usaremos a detecção automática de esquema mas é possível fornecer o esquema in-line (na linha de comando) ou um arquivo JSON que contenha a definição do esquema.

No arquivo "/tmp/file_name.json" estará nossa definição de tabela.

11. Crie uma tabela externa usando como base a definição de tabela criada anteriormente:

bq mk --external_table_definition=/tmp/file_name.json dataset_teste.tb_pessoa

Observação:

Caso já exista uma tabela criada com o mesmo nome, e você queira substitui-la basta utilizar o comando de remoção de tabela:

bq rm --table -f dataset_teste.tb_pessoa

12. Faça um select na tabela criada:

bq query --nouse_legacy_sql \
'SELECT
   *
 FROM
   dataset_teste.tb_pessoa'

Referências