API de execução de instruções: Executar SQL em warehouses

Importante

Para acessar as APIs REST da Databricks, o senhor deve se autenticar.

Este tutorial mostra aos senhores como usar o Databricks SQL Statement Execution API 2.0 para executar SQL declarações de Databricks SQL warehouses.

Para view a referência da API 2.0 do Databricks SQL Statement Execution, consulte Statement Execution.

Antes de começar

Antes de começar este tutorial, certifique-se de que o senhor tenha:

  • Databricks CLI versão 0.205 ou acima ou curl, como segue:

    • A CLI da Databricks é uma ferramenta de linha de comando para enviar e receber solicitações e respostas da API REST da Databricks. Se o senhor usar o Databricks CLI versão 0.205 ou acima, ele deverá ser configurado para autenticação com o seu Databricks workspace. Consulte Instalar ou atualizar a CLI da Databricks e Autenticação para a CLI da Databricks.

      Por exemplo, para se autenticar com a autenticação Databricks personal access token, siga os passos em Databricks personal access tokens para usuários workspace .

      E, em seguida, para usar a CLI do Databricks para criar um perfil de configuração do Databricks para seu access token pessoal, faça o seguinte:

      Observação

      O procedimento a seguir usa o Databricks CLI para criar um perfil de configuração do Databricks com o nome DEFAULT. Se você já tiver um perfil de configuração DEFAULT, este procedimento sobrescreverá seu perfil de configuração DEFAULT existente.

      Para verificar se o senhor já tem um perfil de configuração DEFAULT e para view as configurações desse perfil, se ele existir, use a CLI do Databricks para executar o comando databricks auth env --profile DEFAULT.

      Para criar um perfil de configuração com um nome diferente de DEFAULT, substitua a parte DEFAULT de --profile DEFAULT no comando databricks configure a seguir por um nome diferente para o perfil de configuração.

      1. Use o Databricks CLI para criar um perfil de configuração do Databricks com o nome DEFAULT que utiliza autenticação por access token pessoal do Databricks. Para fazer isso, execute o seguinte comando:

        databricks configure --profile DEFAULT

      2. Para o prompt Databricks Host, digite o URL da instância do espaço de trabalho do Databricks, por exemplo, https://dbc-a1b2345c-d6e7.cloud.databricks.com.

      3. No prompt Personal access token, digite o access token pessoal da Databricks para o seu workspace.

      Nos exemplos de CLI do Databricks deste tutorial, observe o seguinte:

      • Este tutorial pressupõe que o senhor tenha uma variável de ambiente DATABRICKS_SQL_WAREHOUSE_ID em seu computador de desenvolvimento local. Essa variável de ambiente representa o ID do seu depósito Databricks SQL. Essa ID é a sequência de letras e números após /sql/1.0/warehouses/ no campo HTTP path (caminho HTTP ) do seu depósito. Para saber como obter o valor do caminho HTTP do seu depósito, consulte Obter detalhes da conexão para um recurso de computação da Databricks.

      • Se o senhor usar o shell de comando do Windows em vez de um shell de comando para Unix, Linux ou macOS, substitua \ por ^ e ${...} por %...%.

      • Se o senhor usar o shell de comando do Windows em vez de um shell de comando para Unix, Linux ou macOS, nas declarações de documentos JSON, substitua a abertura e o fechamento ' por " e substitua o " interno por \".

    • O curl é uma ferramenta de linha de comando para enviar e receber solicitações e respostas da API REST. Consulte também Instalar curl. Ou adapte os exemplos curl deste tutorial para uso com ferramentas semelhantes, como o HTTPie.

      Nos exemplos curl deste tutorial, observe o seguinte:

      • Em vez de --header "Authorization: Bearer ${DATABRICKS_TOKEN}", o senhor pode usar um .netrc arquivo. Se o senhor usar um arquivo .netrc, substitua --header "Authorization: Bearer ${DATABRICKS_TOKEN}" por --netrc.

      • Se o senhor usar o shell de comando do Windows em vez de um shell de comando para Unix, Linux ou macOS, substitua \ por ^ e ${...} por %...%.

      • Se o senhor usar o shell de comando do Windows em vez de um shell de comando para Unix, Linux ou macOS, nas declarações de documentos JSON, substitua a abertura e o fechamento ' por " e substitua o " interno por \".

      Além disso, para os exemplos curl deste tutorial, este tutorial pressupõe que o senhor tenha a seguinte variável de ambiente em seu computador de desenvolvimento local:

      • DATABRICKS_HOSTrepresentando o nome da instância do espaço de trabalho, por exemplo dbc-a1b2345c-d6e7.cloud.databricks.com, para seu espaço de trabalho Databricks.

      • DATABRICKS_TOKENrepresentando um Databricks pessoal access token para o seu usuário do Databricks workspace.

      • DATABRICKS_SQL_WAREHOUSE_IDrepresentando o ID do seu depósito Databricks SQL. Essa ID é a sequência de letras e números após /sql/1.0/warehouses/ no campo HTTP path (caminho HTTP ) do seu depósito. Para saber como obter o valor do caminho HTTP do seu depósito, consulte Obter detalhes da conexão para um recurso de computação da Databricks.

      Observação

      Como prática recomendada de segurança ao se autenticar com ferramentas, sistemas, scripts e aplicativos automatizados, a Databricks recomenda que você use tokens OAuth.

      Se o senhor usar a autenticação pessoal access token, a Databricks recomenda o uso de pessoal access tokens pertencente à entidade de serviço em vez de usuários workspace. Para criar o site tokens para uma entidade de serviço, consulte gerenciar tokens para uma entidade de serviço.

      Para criar um Databricks pessoal access token, siga as etapas em Databricks pessoal access tokens para usuários workspace .

      Aviso

      A Databricks desaconselha fortemente a codificação de informações em seus scripts, pois essas informações confidenciais podem ser expostas em texto simples por meio de sistemas de controle de versão. A Databricks recomenda que, em vez disso, o senhor use abordagens como a variável de ambiente que define em sua máquina de desenvolvimento. A remoção dessas informações codificadas de seus scripts também ajuda a torná-los mais portáteis.

  • Este tutorial pressupõe que o senhor também tenha o jq, um processador de linha de comando para consultar cargas de resposta JSON, que a Databricks SQL Statement Execution API retorna para o senhor após cada chamada feita para a Databricks SQL Statement Execution API. Veja os downloads jq.

  • O senhor deve ter pelo menos uma tabela na qual possa executar instruções SQL. Este tutorial se baseia na tabela lineitem do esquema tpch (também conhecido como banco de dados) no catálogo samples. Se o senhor não tiver acesso a esse catálogo, esquema ou tabela no site workspace, substitua-os ao longo deste tutorial pelos seus próprios.

passo 1: Execute uma instrução SQL e salve o resultado dos dados como JSON

Execute o seguinte comando, que faz o seguinte:

  1. Usa o endereço SQL warehouse especificado, juntamente com os tokens especificados, se o senhor estiver usando curl, para consultar três colunas das duas primeiras linhas da tabela lineitem no esquema tcph dentro do catálogo samples.

  2. Salva a carga útil da resposta no formato JSON em um arquivo chamado sql-execution-response.json no diretório de trabalho atual.

  3. Imprime o conteúdo do arquivo sql-execution-response.json.

  4. Define uma variável de ambiente local chamada SQL_STATEMENT_ID. Essa variável contém o ID da instrução SQL correspondente. O senhor pode usar esse ID de instrução SQL para obter informações sobre essa instrução posteriormente, conforme necessário, o que é demonstrado no passo 2. Também é possível acessar view essa instrução SQL e obter seu ID de instrução na seção de histórico de consultas do console Databricks SQL ou chamando a APIQuery History .

  5. Define uma variável de ambiente local adicional chamada NEXT_CHUNK_EXTERNAL_LINK que contém um fragmento de URL de API para obter o próximo bloco de dados JSON. Se os dados de resposta forem muito grandes, a API de Execução de Instrução Databricks SQL fornecerá a resposta em partes. Você pode usar esse fragmento de URL da API para obter a próxima parte de dados, que é demonstrada na passo 2. Se não houver próxima parte, essa variável de ambiente será definida como null.

  6. Imprime os valores das variáveis de ambiente SQL_STATEMENT_ID e NEXT_CHUNK_INTERNAL_LINK.

databricks api post /api/2.0/sql/statements \
--profile <profile-name> \
--json '{
  "warehouse_id": "'"$DATABRICKS_SQL_WAREHOUSE_ID"'",
  "catalog": "samples",
  "schema": "tpch",
  "statement": "SELECT l_orderkey, l_extendedprice, l_shipdate FROM lineitem WHERE l_extendedprice > :extended_price AND l_shipdate > :ship_date LIMIT :row_limit",
  "parameters": [
    { "name": "extended_price", "value": "60000", "type": "DECIMAL(18,2)" },
    { "name": "ship_date", "value": "1995-01-01", "type": "DATE" },
    { "name": "row_limit", "value": "2", "type": "INT" }
  ]
}' \
> 'sql-execution-response.json' \
&& jq . 'sql-execution-response.json' \
&& export SQL_STATEMENT_ID=$(jq -r .statement_id 'sql-execution-response.json') \
&& export NEXT_CHUNK_INTERNAL_LINK=$(jq -r .result.next_chunk_internal_link 'sql-execution-response.json') \
&& echo SQL_STATEMENT_ID=$SQL_STATEMENT_ID \
&& echo NEXT_CHUNK_INTERNAL_LINK=$NEXT_CHUNK_INTERNAL_LINK

Substitua <profile-name> pelo nome do perfil de configuração do Databricks para autenticação.

curl --request POST \
https://${DATABRICKS_HOST}/api/2.0/sql/statements/ \
--header "Authorization: Bearer ${DATABRICKS_TOKEN}" \
--header "Content-Type: application/json" \
--data '{
  "warehouse_id": "'"$DATABRICKS_SQL_WAREHOUSE_ID"'",
  "catalog": "samples",
  "schema": "tpch",
  "statement": "SELECT l_orderkey, l_extendedprice, l_shipdate FROM lineitem WHERE l_extendedprice > :extended_price AND l_shipdate > :ship_date LIMIT :row_limit",
  "parameters": [
    { "name": "extended_price", "value": "60000", "type": "DECIMAL(18,2)" },
    { "name": "ship_date", "value": "1995-01-01", "type": "DATE" },
    { "name": "row_limit", "value": "2", "type": "INT" }
  ]
}' \
--output 'sql-execution-response.json' \
&& jq . 'sql-execution-response.json' \
&& export SQL_STATEMENT_ID=$(jq -r .statement_id 'sql-execution-response.json') \
&& export NEXT_CHUNK_INTERNAL_LINK=$(jq -r .result.next_chunk_internal_link 'sql-execution-response.json') \
&& echo SQL_STATEMENT_ID=$SQL_STATEMENT_ID \
&& echo NEXT_CHUNK_INTERNAL_LINK=$NEXT_CHUNK_INTERNAL_LINK

Na solicitação anterior:

  • As consultas parametrizadas consistem no nome de cada parâmetro de consulta precedido por dois pontos (por exemplo, :extended_price) com um objeto name e value correspondente na matriz parameters. Um type opcional também pode ser especificado, com o valor default de STRING se não for especificado.

    Aviso

    A Databricks recomenda enfaticamente que o senhor use parâmetros como uma prática recomendada para suas instruções SQL.

    Se o senhor usar a API Databricks SQL Statement Execution com um aplicativo que gera SQL dinamicamente, isso pode resultar em ataques de injeção de SQL. Por exemplo, se o senhor gerar código SQL com base nas seleções de um usuário em uma interface de usuário e não tomar as medidas adequadas, um invasor poderá injetar código SQL malicioso para alterar a lógica da consulta inicial, lendo, alterando ou excluindo dados confidenciais.

    As consultas parametrizadas ajudam a proteger contra ataques de injeções de SQL, tratando os argumentos de entrada separadamente do restante do código SQL e interpretando esses argumentos como valores literais. Os parâmetros também ajudam na reutilização do código.

  • Em default, todos os dados retornados estão no formato de matriz JSON, e o local default para qualquer resultado de dados da instrução SQL está dentro da carga útil da resposta. Para tornar esse comportamento explícito, adicione "format":"JSON_ARRAY","disposition":"INLINE" ao payload da solicitação. Se o senhor tentar retornar resultados de dados maiores que 25 MiB na carga útil da resposta, um status de falha será retornado e a instrução SQL será cancelada. Para resultados de dados maiores que 25 MiB, o senhor pode usar links externos em vez de tentar retorná-los na carga útil da resposta, o que é demonstrado no passo 3.

  • O comando armazena o conteúdo da carga útil da resposta em um arquivo local. O armazenamento local de dados não é suportado diretamente pela API Databricks SQL Statement Execution.

  • Em default, após 10 segundos, se a instrução SQL ainda não tiver terminado de ser executada no warehouse, a API Databricks SQL Statement Execution retorna apenas o ID da instrução SQL e seu status atual, em vez do resultado da instrução. Para alterar esse comportamento, adicione "wait_timeout" à solicitação e defina-o como "<x>s", em que <x> pode estar entre 5 e 50 segundos inclusive, por exemplo, "50s". Para retornar imediatamente o ID da instrução SQL e seu status atual, defina wait_timeout como 0s.

  • Em default, a instrução SQL continua a ser executada se o período de tempo limite for atingido. Para cancelar uma instrução SQL se o período de tempo limite for atingido, adicione "on_wait_timeout":"CANCEL" ao payload da solicitação.

  • Para limitar o número de bytes retornados, adicione "byte_limit" à solicitação e defina-o como o número de bytes, por exemplo, 1000.

  • Para limitar o número de linhas retornadas, em vez de adicionar uma cláusula LIMIT a statement, o senhor pode adicionar "row_limit" à solicitação e defini-la como o número de linhas, por exemplo, "statement":"SELECT * FROM lineitem","row_limit":2.

  • Se o resultado for maior que o byte_limit ou row_limit especificado, o campo truncated será definido como true na carga útil da resposta.

Se o resultado da declaração estiver disponível antes do término do tempo limite de espera, a resposta será a seguinte

{
  "manifest": {
    "chunks": [
      {
        "chunk_index": 0,
        "row_count": 2,
        "row_offset": 0
      }
    ],
    "format": "JSON_ARRAY",
    "schema": {
      "column_count": 3,
      "columns": [
        {
          "name": "l_orderkey",
          "position": 0,
          "type_name": "LONG",
          "type_text": "BIGINT"
        },
        {
          "name": "l_extendedprice",
          "position": 1,
          "type_name": "DECIMAL",
          "type_precision": 18,
          "type_scale": 2,
          "type_text": "DECIMAL(18,2)"
        },
        {
          "name": "l_shipdate",
          "position": 2,
          "type_name": "DATE",
          "type_text": "DATE"
        }
      ]
    },
    "total_chunk_count": 1,
    "total_row_count": 2,
    "truncated": false
  },
  "result": {
    "chunk_index": 0,
    "data_array": [
      [
        "2",
        "71433.16",
        "1997-01-28"
      ],
      [
        "7",
        "86152.02",
        "1996-01-15"
      ]
    ],
    "row_count": 2,
    "row_offset": 0
  },
  "statement_id": "00000000-0000-0000-0000-000000000000",
  "status": {
    "state": "SUCCEEDED"
  }
}

Se o tempo limite de espera terminar antes que o resultado da declaração esteja disponível, a resposta será semelhante a esta:

{
  "statement_id": "00000000-0000-0000-0000-000000000000",
  "status": {
    "state": "PENDING"
  }
}

Se os dados de resultado da declaração forem muito grandes (por exemplo, neste caso, ao executar SELECT l_orderkey, l_extendedprice, l_shipdate FROM lineitem LIMIT 300000), os dados de resultado serão divididos em pedaços e terão a seguinte aparência. Observe que "...": "..." indica resultados omitidos aqui para fins de brevidade:

{
  "manifest": {
    "chunks": [
      {
        "chunk_index": 0,
        "row_count": 188416,
        "row_offset": 0
      },
      {
        "chunk_index": 1,
        "row_count": 111584,
        "row_offset": 188416
      }
    ],
    "format":"JSON_ARRAY",
    "schema": {
      "column_count":3,
      "columns": [
        {
          "...": "..."
        }
      ]
    },
    "total_chunk_count": 2,
    "total_row_count": 300000,
    "truncated": false
  },
  "result": {
    "chunk_index": 0,
    "data_array": [
      [
        "2",
        "71433.16",
        "1997-01-28"
      ],
      [
        "..."
      ]
    ],
    "next_chunk_index": 1,
    "next_chunk_internal_link": "/api/2.0/sql/statements/00000000-0000-0000-0000-000000000000/result/chunks/1?row_offset=188416",
    "row_count": 188416,
    "row_offset": 0
  },
  "statement_id": "00000000-0000-0000-0000-000000000000",
  "status": {
    "state": "SUCCEEDED"
  }
}

passo 2: obtenha o status de execução atual de uma instrução e o resultado dos dados como JSON

O senhor pode usar o ID de uma instrução SQL para obter o status atual de execução dessa instrução e, se a execução for bem-sucedida, o resultado dessa instrução. Se o senhor esquecer o ID da declaração, poderá obtê-lo na seção de histórico de consultas do console Databricks SQL ou chamando a APIQuery History . Por exemplo, o senhor poderia continuar a sondar esse comando, verificando a cada vez se a execução foi bem-sucedida.

Para obter o status de execução atual de uma instrução SQL e, se a execução tiver sido bem-sucedida, o resultado dessa instrução e um fragmento de URL da API para obter qualquer parte seguinte de dados JSON, execute o seguinte comando. Esse comando pressupõe que o senhor tenha uma variável de ambiente em sua máquina de desenvolvimento local chamada SQL_STATEMENT_ID, que é definida com o valor do ID da instrução SQL do passo anterior. Obviamente, o senhor pode substituir ${SQL_STATEMENT_ID} no comando a seguir pelo ID codificado da instrução SQL.

databricks api get /api/2.0/sql/statements/${SQL_STATEMENT_ID} \
--profile <profile-name> \
> 'sql-execution-response.json' \
&& jq . 'sql-execution-response.json' \
&& export NEXT_CHUNK_INTERNAL_LINK=$(jq -r .result.next_chunk_internal_link 'sql-execution-response.json') \
&& echo NEXT_CHUNK_INTERNAL_LINK=$NEXT_CHUNK_INTERNAL_LINK

Substitua <profile-name> pelo nome do perfil de configuração do Databricks para autenticação.

curl --request GET \
https://${DATABRICKS_HOST}/api/2.0/sql/statements/${SQL_STATEMENT_ID} \
--header "Authorization: Bearer ${DATABRICKS_TOKEN}" \
--output 'sql-execution-response.json' \
&& jq . 'sql-execution-response.json' \
&& export NEXT_CHUNK_INTERNAL_LINK=$(jq -r .result.next_chunk_internal_link 'sql-execution-response.json') \
&& echo NEXT_CHUNK_INTERNAL_LINK=$NEXT_CHUNK_INTERNAL_LINK

Se NEXT_CHUNK_INTERNAL_LINK for definido como um valor diferente denull, o senhor poderá usá-lo para obter o próximo bloco de dados, e assim por diante, por exemplo, com o seguinte comando:

databricks api get /${NEXT_CHUNK_INTERNAL_LINK} \
--profile <profile-name> \
> 'sql-execution-response.json' \
&& jq . 'sql-execution-response.json' \
&& export NEXT_CHUNK_INTERNAL_LINK=$(jq -r .next_chunk_internal_link 'sql-execution-response.json') \
&& echo NEXT_CHUNK_INTERNAL_LINK=$NEXT_CHUNK_INTERNAL_LINK

Substitua <profile-name> pelo nome do perfil de configuração do Databricks para autenticação.

curl --request GET \
https://${DATABRICKS_HOST}${NEXT_CHUNK_INTERNAL_LINK} \
--header "Authorization: Bearer ${DATABRICKS_TOKEN}" \
--output 'sql-execution-response.json' \
&& jq . 'sql-execution-response.json' \
&& export NEXT_CHUNK_INTERNAL_LINK=$(jq -r .next_chunk_internal_link 'sql-execution-response.json') \
&& echo NEXT_CHUNK_INTERNAL_LINK=$NEXT_CHUNK_INTERNAL_LINK

O senhor pode continuar executando o comando anterior, repetidamente, para obter o próximo bloco, e assim por diante. Observe que, assim que o último bloco é obtido, a instrução SQL é encerrada. Após esse encerramento, o senhor não poderá usar o ID dessa declaração para obter seu status atual ou para buscar mais blocos.

passo 4: Cancelar a execução de uma instrução SQL

Se o senhor precisar cancelar uma instrução SQL que ainda não foi bem-sucedida, execute o seguinte comando:

databricks api post /api/2.0/sql/statements/${SQL_STATEMENT_ID}/cancel \
--profile <profile-name> \
--json '{}'

Substitua <profile-name> pelo nome do perfil de configuração do Databricks para autenticação.

curl --request POST \
https://${DATABRICKS_HOST}/api/2.0/sql/statements/${SQL_STATEMENT_ID}/cancel \
--header "Authorization: Bearer ${DATABRICKS_TOKEN}"

Práticas recomendadas de segurança

A API Databricks SQL Statement Execution aumenta a segurança das transferências de dados usando a criptografia TLS (Transport Layer Security) de ponta a ponta e credenciais de curta duração, como URLs pré-assinados.

Há várias camadas nesse modelo de segurança. Na camada de transporte, só é possível chamar o Databricks SQL Statement Execution API usando TLS 1.2 ou acima. Além disso, os chamadores do Databricks SQL Statement Execution API devem ser autenticados com um Databricks pessoal válido access tokenque corresponda a um usuário que tenha o direito de usar o Databricks SQL. Esse usuário deve ter acesso CAN USE para o site SQL warehouse específico que está sendo usado, e o acesso pode ser restrito com listas de acesso IP. Isso se aplica a todas as solicitações para o Databricks SQL Statement Execution API. Além disso, para executar instruções, o usuário autenticado deve ter permissão para os objetos de dados (como tabelas, visualizações e funções) usados em cada instrução. Isso é imposto pelos mecanismos de controle de acesso existentes no Unity Catalog ou pelo uso de ACLs de tabela. (Consulte governança de dados em Unity Catalog para obter mais detalhes). Isso também significa que somente o usuário que executa uma declaração pode fazer solicitações de busca dos resultados da declaração.

A Databricks recomenda as práticas recomendadas de segurança a seguir sempre que o senhor usar a API Databricks SQL Statement Execution junto com a disposição EXTERNAL_LINKS para recuperar grandes conjuntos de dados:

  • Remover o cabeçalho de autorização do Databricks para solicitações do Amazon S3

  • Proteger URLs pré-assinados

  • Configurar restrições de rede na conta de armazenamento

  • Configurar o registro em log na conta de armazenamento

A disposição EXTERNAL_LINKS pode ser desativada mediante solicitação, criando um caso de suporte. Ver Suporte.

Remover o cabeçalho de autorização do Databricks para solicitações do Amazon S3

Todas as chamadas para a API Databricks SQL Statement Execution que usam curl devem incluir um cabeçalho Authorization que contenha credenciais de acesso ao Databricks. Não inclua esse cabeçalho Authorization sempre que o senhor baixar dados do Amazon S3. Esse cabeçalho não é obrigatório e pode expor involuntariamente suas credenciais de acesso ao Databricks.

Proteger URLs pré-assinados

Sempre que o senhor usa a disposição EXTERNAL_LINKS, é gerado um URL predefinido e de curta duração, que o chamador pode usar para baixar os resultados diretamente do Amazon S3 usando TLS. Como uma credencial de curta duração está incorporada nesse URL predefinido, o senhor deve proteger o URL.

Configurar restrições de rede na conta de armazenamento

Sempre que o senhor usar a disposição EXTERNAL_LINKS, o cliente obterá URLs predefinidos para baixar os resultados da consulta diretamente do Amazon S3.

A Databricks recomenda que o senhor use políticas de bucket S3 para restringir o acesso aos buckets S3 a endereços IP e VPCs confiáveis. workspace Verifique se o Databricks senhor tem as configurações corretas VPC de armazenamento do bucket raiz e consulte as recomendações do para gerenciar o cliente .

Configurar o registro em log na conta de armazenamento

Além de impor restrições em nível de rede no armazenamento subjacente account, o senhor pode monitorar se alguém tenta contornar essas restrições configurando o registro de acesso ao servidor S3 ou os eventos de dados do AWS CloudTrail, bem como o monitoramento e os alertas apropriados em torno deles.