Databricks SQL Ano em análise (Parte II): SQL Recurso de programação

Bem-vindo à série de blogs que aborda os avanços do Databricks SQL em 2023, o data warehouse serverless da Databricks. Esta é a parte 2, na qual destacamos muitos dos novos recursos de programação SQL entregues no ano passado. Naturalmente, todo desenvolvedor do SQL deseja ser mais produtivo e lidar com cenários cada vez mais complexos com facilidade. A inclusão de recursos do SQL como esses ajuda os desenvolvedores e nossos clientes a obterem o máximo de seu warehouse Databricks SQL. Tudo isso faz parte da Data Intelligence Platform da Databricks, criada com base na arquitetura lakehouse que combina o melhor do data warehouse e do data lake, e por que o melhor data warehouse é um lakehouse.

Sem mais delongas, aqui estão os destaques do recurso de programação SQL de 2023:

Suporte a alias de coluna lateral

Se o café não é bom para nós, por que todo mundo o toma? O suporte lateral da coluna é assim. Isso vai contra os princípios do SQL, mas é muito útil, pois esse recurso permite que você faça referência ao resultado de uma expressão SQL na lista de seleção em qualquer expressão seguinte nessa mesma lista de seleção. Você olhará para trás e se perguntará como pode ter sido forçado a forçar uma subconsulta apenas para compartilhar uma expressão por tanto tempo em nome da pureza do SQL.

Antes:

        SELECT fullname,               upper(fullname),

               lower(fullname)

          FROM (SELECT name || firstname

                  FROM persons) AS T(fullname);

Depois (com o alias da coluna lateral):

           SELECT name || firstname AS fullname,                  upper(fullname),

                  lower(fullname)

             FROM persons;

Consulte Introdução ao Alias de Coluna Lateral para saber mais.

Error classes e SQLSTATEs

Já faz muito tempo, mas a maioria das condições de erro que você encontra na Databricks apresentará uma classificação de erro legível por humanos e um SQLSTATE baseado no padrão SQL. Essas mensagens de erro estão documentadas e, para Python e Scala, a Databricks também fornece métodos que permitem que você trate as condições de erro programaticamente sem criar uma dependência do texto da mensagem de erro.

Exemplo:

from PySpark.errors import PySparkException



try:

  spark.sql("SELECT * FROM does_not_exist").show()

exceto PySparkException como ex:  if (ex.getErrorClass() == "TABLE_OR_VIEW_NOT_FOUND"):

    print("I'm so sorry, but I cannot find: " + ex.getMessageParameters()['relationName'])

  else:

    raise

Consulte Tratamento de erros no Databricks para saber mais.

Suporte geral a table-valued function

Em 2023, houve muitas melhorias na área de suporte a table-valued function. Começamos generalizando e padronizando a invocação de funções de tabela, de modo que agora você pode invocar todas as funções de tabela na cláusula FROM de uma consulta, até mesmo funções geradoras como explode(), e não há mais necessidade da sintaxe LATERAL view.

Antes:

SELECT *,       explode(ARRAY('x' || Y.y1, 'y' || Y.y1)) AS (z1)

  FROM VALUES('a'), ('b') AS X(c1)

  LATERAL view explode(ARRAY(1, 2)) Y AS y1;

a 1 x1

a 1 y1

a 2 x2

a 2 y2

b 1 x1

b 1 y1

b 2 x2

b 2 y2

Depois:

SELECT *   FROM VALUES('a'), ('b') AS X(c1),

       explode(ARRAY(1, 2)) AS Y(y1),

       LATERAL explode(ARRAY('x' || Y.y1, 'y' || Y.y1)) AS Z(z1);

a 1 x1

a 1 y1

a 2 x2

a 2 y2

b 1 x1

b 1 y1

b 2 x2

b 2 y2

Consulte Invocação de table-valued function para saber mais.

Python UDF e UDTF com polimorfismo

As UDFs de SQL foram introduzidas no Databricks 9 e foram um sucesso estrondoso, mas o pessoal do Python ficou com inveja e aumentou a aposta! Agora você pode:

1. Criar UDFs Python e colocar toda essa lógica brilhante nelas.
2. Passar tabelas para Tabela Python UDFs usando a sintaxe TABLE padrão do SQL. Isso é chamado de polimorfismo, em que o UDF pode se comportar de forma diferente dependendo da assinatura da tabela passada.

Exemplo:

from PySpark.sql.functions import udtffrom PySpark.sql.types import Row



@udtf(returnType="id: int")



    class FilterUDTF:



        def eval(self, row: Row):



            if row["id"] > 5:



                yield row["id"],



spark.udtf.registro("filter_udtf", FilterUDTF)



SELECT * FROM filter_udtf(TABLE(SELECT * FROM range(10)));  6

  7

  8

  9

Consulte Introducing Python User Defined Table Functions, Function invocation | Databricks on AWS, e python_udtf.rst: Table Input Argument para saber mais.

Marcadores de parâmetros sem nome

Em 2022, introduzimos marcadores de parâmetros que permitem que uma consulta SQL faça referência a variáveis reservadas passadas para o SQL usando, por exemplo, o método spark.sql() API. O suporte inicial consistia em marcadores de parâmetros nomeados, o que significa que seus valores Python, Java ou Scala são passados para SQL usando um mapa em que a chave se alinha com o nome do marcador de parâmetro. Isso é ótimo e permite que você se refira ao mesmo argumento repetidamente e fora de ordem.

Em 2023, expandimos o suporte para marcadores de parâmetros não nomeados. Agora, você pode passar uma matriz de valores, e eles são atribuídos em ordem de ocorrência.

Exemplo:

spark.sql("SELECT ? * ? * ? AS volume", args = { 3, 4, 5 }).show()+------+

|volume|

+------+

| 60|

+------+

Consulte Marcadores de parâmetros sem nome para saber mais.

Variáveis de sessão SQL

Os marcadores de parâmetros são ótimos. Nós os amamos. Mas seria ainda melhor se pudéssemos evitar passar os resultados do SQL de volta por meio de dataframes, apenas para voltar e passá-los de volta ao SQL por meio de marcadores de parâmetros. É aí que entram as variáveis de sessão SQL - uma variável de sessão é um objeto escalar (não uma tabela) que é privado para sua sessão SQL, tanto para sua definição quanto para os valores que ela contém. Agora você pode:

1. Declarar uma variável de sessão com um tipo e um valor inicial default.
2. Definir uma ou mais variáveis com base no resultado de uma expressão ou consulta SQL.
3. Variáveis de referência em qualquer consulta ou instrução DML.

Essa é uma ótima maneira de dividir as consultas e passar o estado de uma consulta para outra.

Exemplo:

DECLARE var INTEGER default 5;SELECT var;

5

SET VAR var = (SELECT max(c1) * var FROM VALUES(1), (2), (3) AS T(c1));

SELECT var;

15

Consulte Variáveis para saber mais.

Cláusula IDENTIFIER

Nos dois destaques anteriores, mostramos como parametrizar as consultas com valores passados pelo aplicativo ou pelo notebook, ou até mesmo usando variáveis de sessão pesquisadas em uma tabela. Mas você também não quer parametrizar identificadores, por exemplo, nomes de tabelas, nomes de funções e outros, sem se tornar o alvo de uma piada do XKCD sobre injeção de SQL. A cláusula IDENTIFIER permite que você faça exatamente isso. Ele transforma magicamente valores de strings em variáveis de sessão ou fornecidos por meio de marcadores de parâmetros em nomes SQL para serem usados como referências a funções, tabelas ou colunas.

Exemplo:

DECLARE agg = 'max';DECLARE col = 'c1';

DECLARE tab = 'T';

CREATE TEMPORARY view IDENTIFIER(tab)(c1, c2) AS (VALUES ('a', 'b'), ('c', 'd'));

SELECT IDENTIFIER(agg)(IDENTIFIER(col)) FROM IDENTIFIER(tab);

c

Consulte a cláusula IDENTIFIER para saber mais.

INSERT BY NAME

O INSERT BY NAME é um recurso de usabilidade interessante que faz você se perguntar por que o SQL não foi criado dessa forma para lidar com tabelas amplas (ou seja tabelas com muitas colunas). Quando você lida com muitas colunas, levante a mão se você gosta de procurar a ordem em que deve fornecer as colunas na lista de seleção que alimenta o INSERT. Ou você prefere soletrar a longa lista de colunas do alvo de inserção? Ninguém sabe.

Agora, em vez de fornecer essa lista de colunas e verificar e verificar novamente a ordem da lista de seleção, você pode dizer ao Databricks para fazer isso por você. Basta INSERT BY NAME, e o Databricks alinhará sua lista de seleção com as colunas da tabela.

Exemplo:

CREATE TABLE T(c1 INT, c2 INT);INSERT INTO T BY NAME SELECT 1 AS c2, 2 AS c1;

SELECT * FROM T;

2 1

Consulte INSERT INTO para saber mais.

Named Parameter invocation

Imagine que você escreveu uma função que recebe 30 argumentos e a maioria deles tem um default sensato. Mas agora você deve invocá-lo com esse último argumento, que não é o default. Basta "ignorar" e definir apenas esse parâmetro, sem se preocupar com a ordem dos argumentos! Basta informar ao argumento para qual parâmetro ele se destina.

Exemplo:

CREATE FUNCTION my_tan(sin FLOAT, cos FLOAT) RETURN sin / cos;SELECT my_tan(cos => 0.4, sin => 0.1);

0.25

Consulte Named Parameter Invocation para saber mais.

TIMESTAMP sem fuso horário

Por padrão, os carimbos de data e hora da Databricks são "com fuso horário local". Quando você fornecer um carimbo de data/hora, a Databricks assumirá que ele está no fuso horário de sua localidade e o armazenará normalizado para UTC. Quando você a lê novamente, essa tradução é desfeita e parece boa. Se, no entanto, outro usuário ler o carimbo de data/hora de outro fuso horário, ele verá o carimbo de data/hora normalizado traduzido para o fuso horário dele.

Esse é um ótimo recurso, a menos que você queira armazenar apenas um carimbo de data/hora "como é". TIMESTAMP_NTZ é um novo tipo que considera o tempo pelo seu valor nominal. Se você der a ele o horário das 14h do dia 4 de janeiro de 2024, ele o armazenará.

Exemplo:

SET TIME ZONE 'America/Los_Angeles';DECLARE local_time TIMESTAMP_LTZ = TIMESTAMP'2023-12-01 12:13:14'; DECLARE any_time TIMESTAMP_NTZ = TIMESTAMP'2023-12-01 12:13:14';



DECLARAR any_time TIMESTAMP_NTZ = TIMESTAMP'2023-12-01 12:13:14';



SELECT local_time, any_time;



2023-12-01 12:13:14 2023-12-01 12:13:14



SET TIME ZONE 'America/New_York';

SELECT local_time, any_time;

2023-12-01 15:13:14 2023-12-01 12:13:14

Consulte Introdução ao TIMESTAMP_NTZ para saber mais.

Suporte a consultas federadas

É claro que sabemos que todos os seus dados já estão no lakehouse. Mas se você tiver amigos que ainda têm alguns dados em outro lugar, diga a eles que não se preocupem. Eles ainda podem acessar esses dados em Databricks registrando essas tabelas estrangeiras no Databricks Unity Catalog e executando todas as consultas em SQL sem precisar sair de Databricks. Basta registrar uma conexão com o sistema remoto, vincular um catálogo remoto (também conhecido como banco de dados) e consultar o conteúdo. É claro que você pode misturar e combinar tabelas locais e estrangeiras na mesma consulta.

Exemplo:

CREATE CONNECTION postgresql_connection    TIPO POSTGRESQL

    OPÇÕES (

      host 'qf-postgresql-demo.xxxxxx.us-west-2.rds.amazonaws.com',

      porta '5432',      usuário 'postgresql_user',



      password 'password123');



CREATE FOREIGN CATALOG pg

    USANDO A CONEXÃO postgresql_connection

    OPTIONS (banco de dados 'postgresdb');

SELECT * FROM pg.myschema.t;

Consulte Federated Queries para saber mais.

Segurança em nível de linha e mascaramento de coluna

Está se sentindo reservado? Você precisa dar a alguns usuários acesso à sua tabela, mas prefere não mostrar todos os seus segredos? A segurança em nível de linha e o mascaramento de coluna são o que você precisa. É possível conceder a outros usuários e grupos acesso a uma tabela, mas estabelecer regras personalizadas sobre as linhas que eles podem ver. Você pode até mesmo apagar ou ofuscar as PII (informações de identificação pessoal), como substituir todos os dígitos do número do cartão de crédito, exceto os três últimos, por estrelas.

Para adicionar um filtro de linha, crie um UDF que determine se o usuário pode ver uma linha com base nos argumentos da função. Em seguida, adicione o filtro de linha à sua tabela usando ALTER TABLE ou faça isso em CREATE TABLE.

Exemplo:

CREATE FUNCTION us_filter(region strings)RETURN IF(IS_ACCOUNT_GROUP_MEMBER('admin'), true, region='US');

CREATE TABLE ventas (strings da região, id INT);

ALTER TABLE ventas SET ROW FILTER us_filter ON (region);

Para adicionar uma máscara de coluna:
Crie um UDF que receba dados de um determinado tipo, modifique-os com base no usuário e retorne o resultado. Em seguida, anexe a máscara à coluna quando você criar a tabela ou usar ALTER TABLE.

Exemplo:

CREATE FUNCTION ssn_mask(ssn strings)  RETURN CASE WHEN is_member('HumanResourceDept') THEN ssn ELSE '***-**-****' END;

CREATE TABLE usuários (

  nome strings,

  ssn strings MASK ssn_mask);

SELECT * FROM users;

Jack ***-**-***

Consulte Filtros de linha e Máscaras de coluna para saber mais.

GROUP BY ALL e ORDER BY ALL

Aqui está você. Você elaborou uma bela consulta de relatório e recebeu um erro "MISSING_AGGREGATION" porque o SQL fez com que você listasse todas as colunas de agrupamento que já havia listado anteriormente novamente na cláusula GROUP BY.

"Faça uma lista! Verifique duas vezes!" é um ótimo conselho para alguns. Para outros, nem tanto.

Para isso, agora você pode dizer ao Databricks para fazer o trabalho por você e coletar todas as colunas para agrupar.

E, já que estamos falando disso, ordene também o conjunto de resultados por todas as colunas retornadas, se quiser.

Exemplo:

SELECT name, firstname, level, sum(comp) as totalcomp  FROM VALUES('The Cricket', 'Jimmy' , 'Principal Conscience', 2),

             ('Geppetto' , 'Signore', 'Woodcarver' , 1)

       AS emp(name, firstname, level, empid)

  NATURAL join VALUES(1, 200, 'salary'),

                     (1, 100, 'spot' ),

                     (2, 250, 'salary'),

                     (2, 120, 'spot' )

       AS pay(empid, comp, reason)

  GROUP BY ALL

  ORDER BY ALL;

Gepeto Escultor de madeira do Signore 300

O Grilo Jimmy Consciência Principal 370

Consulte GROUP BY, ORDER BY para saber mais.

Mais funções SQL built-in

Há duas certezas na vida de um desenvolvedor: Nunca há chá de boba suficiente e nunca há funções integradas suficientes. Além de várias funções para aumentar a compatibilidade com outros produtos, como to_char e to_varchar em tipos de data e hora, nos concentramos em ampliar bastante o conjunto de funções de manipulação de matriz, bem como a biblioteca de funções de bitmap e hll_sketch. Cada uma das funções de bitmap pode acelerar a contagem de consultas de estilos distintos sobre números inteiros. Já os datasketches permitem uma ampla variedade de recursos de contagem probabilística.

Exemplo:

SELECT mask('AaBb123-&^ % 서울 Ä', lowerchar => 'z', otherchar => 'X');AzBz123XXXXXXXXXÄSELECT sum(num_distinct) AS num_distinct



FROM(SELECT bitmap_bucket_number(val),



  bitmap_count(bitmap_construct_agg(bitmap_bit_position(val)) FROM VALUES(1), (2), (1), (-1), (5), (0), (5) AS t(val) GROUP BY ALL) AS distinct_vals_by_bucket(bucket, num_distinct);



5



SELECT hll_sketch_estimate(



  hll_sketch_agg(col))



FROM VALUES('abc'), ('def'), ('abc'), ('ghi'), ('abc') AS tab(col);



3

Consulte Função Mask, função bitmap_count, função to_varchar, sketch based approximate distinct counting para saber mais.

Databricks ❤️ SQL

Na Databricks, gostamos tanto de SQL que batizamos nosso data warehouse com esse nome! E, como o melhor data warehouse é um lakehouse, SQL e Python têm uma experiência de primeira classe em toda a Databricks Intelligent Data Platform. Estamos entusiasmados em adicionar novos recursos, como os acima, para ajudar nossos clientes a usar o SQL em seus projetos, e já estamos trabalhando em outros.

Se você deseja migrar seus workloads SQL para um ambiente de alto desempenho, serverless data warehouse com um excelente ambiente para desenvolvedores SQL, então Databricks SQL é a solução - experimente-o gratuitamente.