Revisión anual de SQL fácil de usar de BigQuery

En esta edición de fin de año Google muestra las nuevas capacidades de SQL que lanzan en las categorías de clase empresarial, calidad de datos y operaciones de esquema con BigQuery.

Clase empresarial

Para los analistas de datos empresariales, compartimos las nuevas capacidades en BigQuery SQL que permiten administrar las cargas de trabajo con la misma familiaridad que pueden haber usado en las bases de datos y los almacenes de datos heredados.

Sesiones (GA)

Una sesión de base de datos (o sesión) representa una conexión entre un usuario o un programa y una base de datos o almacén de datos que almacena y consulta datos. Un analista de datos a través de una herramienta o un programa puede ejecutar varios comandos que se rastrean en esta sesión. BigQuery en esencia es un motor de consultas sin estado, lo que significa que no mantiene una conexión de red persistente, como lo hacen las conexiones de base de datos tradicionales. Esto permite que BigQuery escale la informática y el almacenamiento de forma independiente y sin límites.

Para aquellos administradores de datos empresariales que buscan administrar la actividad de BigQuery a través de sesiones, pueden habilitar opcionalmente la compatibilidad con sesiones en BigQuery mediante la interfaz de usuario, la API y la CLI o pueden configurar el parámetro EnableSession en el controlador JDBC u ODBC . Esto permite a las empresas acumular todos los beneficios de las sesiones sin la sobrecarga de una conexión persistente.

Para los analistas de datos, las sesiones brindan los beneficios de:

Variables de sesión: que permite la creación de una variable específica de sesión que persiste para todos los comandos que se ejecutan en la sesión.
Variables del sistema: variables preconstruidas para zona horaria, identificadores de conjuntos de datos, proyectos, identificadores de sesión, que, si se especifican, permiten a los analistas de datos anular los valores predeterminados del nivel de organización o proyecto con configuraciones específicas de la sesión.
Tablas temporales y funciones temporales de duración de la sesión: cuando se utilizan tablas temporales en una sesión para organizar los resultados o se utiliza una función temporal específica de la sesión para hacer que las operaciones de consulta sean eficientes, se conservan durante toda la sesión y son accesibles para todos. trabajos en esa sesión.

Para los administradores de datos, los beneficios son:

Etiquetas de sesión: al asignar etiquetas para el seguimiento de trabajos, los administradores de datos pueden encontrar todas las actividades asociadas con esa sesión usando esa etiqueta en el registro de auditoría.
Gestión de sesiones: los administradores de datos pueden finalizar manualmente las sesiones llamando al procedimiento del sistema BQ.ABORT_SESSION con el ID de la sesión como entrada.

Las sesiones de BigQuery también admiten consultas y transacciones de varias declaraciones. Documentación.

Nombres de tablas y conjuntos de datos que no distinguen entre mayúsculas y minúsculas (versión preliminar)

Los nombres de las tablas y los nombres de los conjuntos de datos de BigQuery distinguen entre mayúsculas y minúsculas, de forma predeterminada, lo que refleja sus orígenes en el análisis de big data, donde los nombres de los archivos de los conjuntos de datos han mantenido la distinción entre mayúsculas y minúsculas. Para volverse más fácil de usar con las herramientas y los programas traídos por los analistas de datos de sus almacenes de datos heredados, BigQuery ahora admite nombres de tabla y nombres de esquema (conjunto de datos) que no distinguen entre mayúsculas y minúsculas, de modo que un conjunto de datos.Table ≡ DaTaSeT.TABLE ≡ Dataset.table ≡ DATASET.tabLe en BigQuery. Puede configurar esto en la definición (DDL) para el conjunto de datos o la tabla mediante la opción is_case_insensitive. Documentación

-- Create a case-insensitive dataset CREATE SCHEMA mydataset OPTIONS(is_case_insensitive=TRUE) -- Create a table in the case-insensitive dataset. CREATE TABLE mydataset.table1(id STRING); -- Query table with different casing SELECT * FROM myDATASET.TaBLE1;

Calidad de datos

Dado que las plataformas de análisis de datos, como BigQuery, traen datos de diferentes fuentes para ayudar en la toma de decisiones, los ingenieros de datos deben mantener la calidad de los elementos de datos críticos. Las fuentes de datos pueden ser imperfectas y tener información incompleta. Por lo tanto, los ingenieros de datos deben configurar una lógica predeterminada inteligente cuando faltan datos de columna para garantizar que se completen los datos correctos. Del mismo modo, las canalizaciones de datos deben proporcionar una lógica flexible para los datos numéricos a fin de reducir el sesgo y garantizar resultados precisos. Los analistas de datos deben hacer coincidir la información de texto independientemente del caso para garantizar un informe preciso.

Valor de columna predeterminado (Vista previa)

Cuando se insertan filas nuevas en una tabla, es posible que algunas columnas no tengan datos. La expresión de valor predeterminado permite la especificación de un valor predeterminado utilizando un valor literal o una función que calcula el valor predeterminado cuando faltan los datos de la columna asociada. La especificación del valor predeterminado se puede establecer al crear una nueva tabla o al modificar las propiedades de columna de una tabla existente. Además, las declaraciones DML como INSERT o CREATE TABLE AS SELECT o MERGE permiten la especificación de la lógica predeterminada en lugar de un valor insertado real que utiliza la especificación predeterminada de la columna en la que se inserta. Documentación

lang-sql

-- Create a table with a default value like CURRENT_TIMESTAMP() etc..
CREATE OR REPLACE TABLE mydataset.orders 
(
  customer STRING DEFAULT 'foo',
  order_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP(),
  user STRING DEFAULT SESSION_USER(),
  details STRUCT<a STRING, b STRING> DEFAULT ('a1', 'b1')
);
​
-- Insert to table with omitted columns
INSERT INTO mydataset.orders(customer, user) 
VALUES ('customer1', DEFAULT),
       ('customer2', 'David');
​
-- Validates default value are used
SELECT * FROM mydataset.orders;

Intercalación de cadenas que no distingue entre mayúsculas y minúsculas (versión preliminar)

Una especificación de intercalación cuando se asocia con una operación de columna, por ejemplo, unión, comparación o cláusula ORDER BY o GROUP BY, determina la lógica utilizada para comparar u ordenar valores de cadena. Por ejemplo, la intercalación específica predeterminada en BigQuery es 'binaria', que usa el orden del punto de código para especificar la secuencia de ordenación en Unicode por la cual todas las letras mayúsculas [AZ] preceden a las letras minúsculas [az]. Nos complace ofrecer una intercalación que no distingue entre mayúsculas y minúsculas, especificada mediante 'und:ci' mediante la cual [A,a] se tratará como caracteres equivalentes y precederá a [B. b] para operaciones de valor de cadena. Esto permite a los analistas de datos encontrar valores de cadena coincidentes independientemente del caso al tratar a Maclean y MacLean como equivalentes. Documentación

lang-sql

-- Create a table with case-insensitive string columns 
CREATE or replace TABLE mydataset.table1
(    
   id INT64,
   column_ci STRING COLLATE 'und:ci'
);
 
insert into mydataset.table1
values (1,"alpha"),(2,"BETA"), (3,"Alpha");
 
-- Filter by column_ci case-insensitively
SELECT * FROM `mydataset`.table1 WHERE column_ci = 'Alpha';
 
-- Sort by column_ci case-insensitively
SELECT * FROM mydataset.table1 ORDER BY column_ci;
 
-- Group by column_ci case-insensitively
SELECT column_ci, sum(id) FROM mydataset.table1 GROUP BY column_ci;

Redondeo bancario (GA)

BigQuery ha estado redondeando de forma nativa todos los valores que exceden la máxima precisión usando la lógica de redondeo más común: "redondear la mitad lejos de cero". Sin embargo, el uso exclusivo de "Redondear la mitad lejos de cero" puede introducir un sesgo de redondeo en los datos al hacer que la operación agregada, por ejemplo, la suma o el promedio, se desvíe del valor real. Otras técnicas de redondeo, como el redondeo gaussiano, comúnmente conocido como redondeo bancario, proporcionan un esquema de redondeo alternativo que no sufre de sesgo negativo o positivo tanto como el método de redondeo a la mitad de cero sobre agregaciones sobre distribuciones más razonables. Con esto, BigQuery se complace en presentar compatibilidad con múltiples modos de redondeo para columnas en definiciones de tablas y en la función ROUND explícita: el modo de redondeo existente, llamado "redondear_la mitad_de_cero" y un nuevo modo, Redondeo de banqueros, también conocido como "round_half_even". El modo "round_half_even" redondea hacia el "vecino" más cercano a menos que ambos vecinos sean equidistantes, en cuyo caso, redondea hacia el vecino par, por ejemplo, 3.1 y 3.2 se redondearán a 3 y 3.5 y 4.5 se redondearán a 4.Documentación

lang-sql

-- ROUND_HALF_EVEN will round down 2.25 to 2.2
SELECT ROUND(NUMERIC "2.25", 1, "ROUND_HALF_EVEN");
 
-- Create a table with 'ROUND_HALF_EVEN' numeric column
CREATE or REPLACE TABLE mydataset.table1 (
 x NUMERIC(5,2) OPTIONS (rounding_mode='ROUND_HALF_EVEN'),
 y NUMERIC(5,2) OPTIONS (rounding_mode='ROUND_HALF_AWAY_FROM_ZERO')
);
 
-- Insert values into the new columns, values that are in halfway between 2.22 and 2.23
INSERT INTO mydataset.table1 VALUES (2.225, 2.225);
 
-- Select values and view how they were rounded to scale of 2.
SELECT * FROM mydataset.table1;

Operaciones de esquema

Continuando con la expansión de la sintaxis de SQL para las operaciones de esquema, BigQuery se complace en presentar dos nuevas capacidades de SQL: LOAD DATA para cargar datos en tablas y RENAME columna para permitir que se cambie el nombre de las columnas de tablas existentes.

LOAD DATA (versión preliminar)
Esta declaración proporciona a los ingenieros y analistas de datos una interfaz SQL para cargar uno o más archivos en las tablas. Brinda la capacidad de cargar en una tabla con nombre, crear una tabla nueva o truncar una tabla existente como parte de la carga de todos los tipos de archivos compatibles con BigQuery, incluidos CSV, AVRO, Parquet, JSON, etc. Además de especificar opciones de la tabla, como los metadatos de la tabla o la caducidad de la tabla, el comando LOAD DATA también permite a los usuarios especificar esquemas de partición y agrupación como parte de la especificación de la tabla o desde una especificación de partición de colmena para archivos externos.

LOAD DATA usa el mismo grupo de recursos que la API de carga de BigQuery para cargar datos. Si los clientes de espacios fijos han configurado una reserva de PIPELINE, LOAD DATA utilizará los espacios de cómputo dedicados de la asignación de reserva de PIPELINE. Documentación

lang-sql

-- Load a CSVfile into a table.
LOAD DATA INTO mydataset.table6
 FROM FILES(
   format='CSV',
   uris = ['gs://cloud-samples-data/bigquery/us-states/us-states-by-date.csv']
 );
 
 select * from mydataset.table6;
​
-- Truncate table and load a CSV file into it.
LOAD DATA OVERWRITE mydataset.table6
 FROM FILES(
   format='CSV',
   uris = ['gs://cloud-samples-data/bigquery/us-states/us-states-by-date.csv']
 );
 
 select * from mydataset.table6;
 
-- Load a CSV file into a table with schema, partitioning and clustering
LOAD DATA INTO mydataset.table7(name STRING, post_abbr STRING, date DATE)
 PARTITION BY date
 CLUSTER BY post_abbr
 FROM FILES(
   skip_leading_rows=1,
   format='CSV',
   uris = ['gs://cloud-samples-data/bigquery/us-states/us-states-by-date.csv']
 );
 
select * from mydataset.table7;
-- Load hive partitioned PARQUET files, infer partition layout
LOAD DATA INTO mydataset.table8
 FROM FILES(
   format='PARQUET',
   uris = ['gs://cloud-samples-data/bigquery/hive-partitioning-samples/autolayout/*'],
   hive_partition_uri_prefix='gs://cloud-samples-data/bigquery/hive-partitioning-samples/autolayout'
 )
 WITH PARTITION COLUMNS;
select * from mydataset.table8;

Columna RENOMBRAR (Vista previa)

RENAME COLUMN permite a los usuarios cambiar el nombre de una columna en una tabla existente; este es un cambio de metadatos solamente. Anteriormente, cambiar el nombre de las columnas requería una solución alternativa que requería que se reescribieran los datos de la columna. Ahora, cuando se debe cambiar el nombre de una columna porque el nombre actual es obsoleto o se nombró incorrectamente por error, los propietarios de los datos pueden ejecutar un comando solo de metadatos de costo cero, RENOMBRAR columna, para arreglar y corregir el nombre de la columna. Documentación

lang-sql

-- Rename two columns 
CREATE or replace TABLE mydataset.table9
(columnA int64, columnB string);
select column_name, ordinal_position, data_type
from information_schema.columns
where table_schema = “mydataset”
and table_name = "table9";
ALTER TABLE mydataset.table9
 RENAME COLUMN columnA TO columnA1,
 RENAME COLUMN IF EXISTS columnB TO columnB1;
select column_name, ordinal_position, data_type
from information_schema.columns
where table_schema = “mydataset”
and table_name = "table9";
-- Swap two columns
ALTER TABLE mydataset.table9
 RENAME COLUMN columnA1 TO temp,
 RENAME COLUMN columnB1 TO columnA,
 RENAME COLUMN temp TO columnB;
 select column_name, ordinal_position, data_type
from information_schema.columns
where table_schema = “mydataset”
and table_name = "table9";

A medida que cerramos este año, nos entusiasma traerles nuevas capacidades en el nuevo año, nuestros aficionados a los datos de BigQuery, para hacer que BigQuery SQL sea más fácil de usar que nunca.

Post traducido del Blog de Google Maps y basado en esta publicación.

Si quieres conocer todas las actualizaciones del sector IT y conocernos un poco más puedes suscribirte a nuestra Newsletter, seguirnos en Twitter o Linkedin.

Tweet to @extramilecloud

ExtraMile Cloud

Buscar este blog