Las funciones integradas amplían el poder de SQL con transformaciones específicas de valores para necesidades comunes y casos de uso. Por ejemplo, el LOG10 función acepta un argumento de entrada numérico y devuelve el logaritmo con base 10 como un resultado de punto flotante de doble precisión, y el LOWER función acepta una cadena y devuelve el resultado de convertir cada carácter a minúsculas.
como parte de nuestro compromiso Para facilitar la migración de sus cargas de trabajo de almacenamiento de datos a la plataforma Lakehouse de Databricks, durante el último año hemos diseñado cuidadosamente y lanzado docenas de nuevas funciones integradas en el dialecto SQL estándar appropriate con ANSI. La comunidad Apache Spark de código abierto también ha realizado contribuciones significativas a esta área, que también hemos integrado en el tiempo de ejecución de Databricks. En esta publicación de weblog, mencionamos un subconjunto útil de estas nuevas funciones y describimos, con ejemplos, cómo pueden resultar útiles para sus viajes de procesamiento de datos en los próximos días. ¡Por favor, disfruta!
Procesar cadenas y buscar elementos
Use Databricks SQL para inspeccionar y procesar rápidamente cadenas con nuevas funciones en esta categoría. Puede verificar rápidamente si una cadena contiene una subcadena, inspeccione su longitud, dividir cuerdas y verifique prefijos y sufijos.
> SELECT incorporates('SparkSQL', 'SQL'),
incorporates('SparkSQL', 'Spork')
true, false
> SELECT len('Spark SQL ');
10
> SELECT split_part('Hiya,world,!', ',', 1);
Hiya
> SELECT startswith('SparkSQL', 'Spark'),
endswith('SparkSQL', 'dataframes');
true, false
Usar operaciones de expresiones regulares para comparar cadenas con patrones o funciones especializadas para convertir hacia o desde números usando formatos especializados, y hacia y desde URL patrones.
> WITH w AS (SELECT
'Steven Jones and Stephen Smith' AS goal,
'Ste(v|ph)en' AS sample)
-- Return the primary substring that matches the sample.
SELECT regexp_substr(goal, sample) FROM w;
Steven
-- This format string expects:
-- * an optionally available signal in the beginning,
-- * adopted by a greenback signal,
-- * adopted by a quantity between 3 and 6 digits lengthy,
-- * 1000's separators,
-- * as much as two digits past the decimal level.
> SELECT to_number('-$12,345.67', 'S$999,099.99');
-12345.67
-- This format string produces 5 characters earlier than the decimal level and two after.
> SELECT '(' || to_char(123, '99999.99') || ')';
(123.00)
> SELECT url_decode('httppercent3Apercent2Fpercent2Fspark.apache.orgpercent2Fpathpercent3Fquerypercent3D1');
http://spark.apache.org/path?question=1
Comparar números y marcas de tiempo
Entra en los detalles por extrayendo bits y realizar lógica condicional en números enteros y de coma flotante. Convierta números de punto flotante en enteros redondeando arriba o abajo con una escala objetivo opcional, o comparar números para igualdad con soporte para valores NULL.
> SELECT bit_get(23Y, 3),
bit_get(23Y, 0);
0, 1
> SELECT ceil(5.4),
ceil(-12.345, 1);
6, -12.3
> SELECT ground(3345.1, -2);
3300
> SELECT equal_null(2, 2),
equal_null(2, 1),
equal_null(NULL, NULL),
equal_null(NULL, 1);
true, false, true, false
Trabaje con valores temporales utilizando nuevas conversiones fuertemente tipadas. Elenco expresión de entrada hacia o desde uno de los tipos de datos INTERVALO, consulte el fecha preciseo agregar y sustraer a fechas y marcas de tiempo.
> SELECT forged(INTERVAL '12:04.9900' MINUTE TO SECOND AS STRING);
INTERVAL '12:04.99' MINUTE TO SECOND
> SELECT curdate()
2022-12-21
-- March 31, 2022 minus 1 month yields February 28, 2022.
> SELECT dateadd(MONTH, -1, TIMESTAMP'2022-03-31 00:00:00');
2022-02-28 00:00:00.000000
-- One month has handed regardless that it is not the tip of the month but as a result of
-- the day and time line up.
> SELECT datediff(MONTH, TIMESTAMP'2021-02-28 12:00:00', TIMESTAMP'2021-03-28 12:00:00');
1
Trabajar con arreglos, estructuras y mapas
Realice consultas sofisticadas para sus datos estructurados y semiestructurados con el formación, estructuray mapa tipos Construya nuevos valores de matriz con el formación constructor, o inspeccione los arreglos existentes para ver si contienen valores específicos o averiguar cuáles son sus posiciones son. Controlar cuantos elementos están en una matriz, o extraen información específica elementos por índice.
-- This creates an array of integers.
> SELECT array(1, 2, 3);
(1,2,3)
> SELECT array_contains(array(1, 2, 3), 2),
array_position(array(3, 2, 1, 4, 1), 1);
true, 3
> SELECT array_size(array(1, NULL, 3, NULL));
4
> SELECT get(arr, 0), get(arr, 2), arr(2) FROM VALUES(array(1, 2, 3)) AS T(arr);
1, 3, 3
> SELECT element_at(array(1, 2, 3), 2),
try_element_at(array(1, 2, 3), 5);
2, NULL
Los mapas son un tipo de datos poderoso que admite la inserción de claves únicas asociadas con valores y su posterior extracción eficiente. Utilizar el mapa constructor para crear nuevos valores de mapa y luego buscar valores más tarde según sea necesario. Una vez creado, puede concatenar juntarlos, o extraer sus llaves o valores como matrices.
> SELECT map(1.0, '2', 3.0, '4');
{1.0 -> 2, 3.0 -> 4}
> SELECT map_contains_key(map(1, 'a', 2, 'b'), 2);
true
> SELECT map_concat(map(1, 'a', 2, 'b'), map(3, 'c'));
{1 -> a, 2 -> b, 3 -> c}
> SELECT map_keys(map(1, 'a', 2, 'b')),
map_values(map(1, 'a', 2, 'b'));
(1,2), (a,b)
Realizar cálculos a prueba de errores
Disfrute de los beneficios de SQL estándar con el modo ANSI mientras evita que sus canalizaciones ETL de ejecución prolongada devuelvan errores con nuevas funciones a prueba de errores. Cada función de este tipo devuelve NULL en lugar de generar una excepción. Por ejemplo, eche un vistazo a probar_añadir, probar_restar, intentar_multiplicary prueba_divide. También puedes realizar yesoscalcular sumas y promediosy con seguridad convertir valores hacia y desde números y marcas de tiempo utilizando opciones de formato personalizadas.
> SELECT try_divide(3, 2), try_divide(3 , 0);
1.5, NULL
> SELECT try_cast('10' AS INT);
10
> SELECT try_cast('a' AS INT);
NULL
> SELECT try_sum(col) FROM VALUES (5), (10), (15) AS tab(col);
30
> SELECT try_avg(col) FROM VALUES (5e37::DECIMAL(38, 0)), (5e37::DECIMAL(38, 0)) AS tab(col);
NULL
-- A plus signal is optionally available within the format string, and so are fractional digits.
> SELECT try_to_number('$345', 'S$999,099.99');
345.00
-- The quantity format requires a minimum of three digits.
> SELECT try_to_number('$45', 'S$999,099.99');
NULL
Agregar grupos de valores de nuevas maneras
Tome decisiones basadas en datos haciendo preguntas sobre grupos de valores utilizando nuevos funciones agregadas incorporadas. Por ejemplo, ahora puede volver algún valor en un grupo, concatenar grupos en arreglosy calcular histogramas. También puede realizar cálculos estadísticos consultando el mediana o modo de un grupo, o sea específico buscando cualquier percentil.
> SELECT any_value(col) FROM VALUES (10), (5), (20) AS tab(col);
10
> SELECT array_agg(col) FROM VALUES (1), (2), (NULL), (1) AS tab(col);
(1,2,1)
> SELECT histogram_numeric(col, 5) FROM VALUES (0), (1), (2), (10) AS tab(col);
({"x":0,"y":1.0},{"x":1,"y":1.0},{"x":2,"y":1.0},{"x":10,"y":1.0})
> SELECT median(DISTINCT col) FROM VALUES (1), (2), (2), (3), (4), (NULL) AS tab(col);
2.5
-- Return the median, 40%-ile and 10%-ile.
> SELECT percentile_cont(array(0.5, 0.4, 0.1)) WITHIN GROUP (ORDER BY col)
FROM VALUES (0), (1), (2), (10) AS tab(col);
(1.5, 1.2000000000000002, 0.30000000000000004)
El nuevo regr_* la familia de funciones te ayuda a preguntar preguntas acerca de valores de un grupo donde las expresiones de entrada NO son NULAS.
-- Returns the intercept of the univariate linear regression line.
> SELECT regr_intercept(y, x) FROM VALUES (1, 2), (2, 3), (2, 3), (null, 4), (4, null) AS T(y, x);
0.7777777777777779
-- Returns the coefficient of dedication from the values.
> SELECT regr_r2(y, x) FROM VALUES (1, 2), (2, 3), (2, 3), (null, 4), (4, null) AS T(y, x);
1
-- Returns the sum of squares of one of many enter expression values of a bunch.
> SELECT regr_sxy(y, x) FROM VALUES (1, 2), (2, 3), (2, 3), (null, 4), (4, null) AS T(y, x);
0.6666666666666666
Cada uno de estos también puede ser invocado como un función de ventana utilizando la cláusula OVER.
Usar encriptación
Proteja el acceso a sus datos mediante cifrar esta en reposo y descifrando cuando sea necesario. Estas funciones utilizan el Estándar de cifrado avanzado (AES) para convertir valores hacia y desde sus equivalentes encriptados.
> SELECT base64(aes_encrypt('Spark', 'abcdefghijklmnop'));
4A5jOAh9FNGwoMeuJukfllrLdHEZxA2DyuSQAWz77dfn
> SELECT forged(aes_decrypt(unbase64('4A5jOAh9FNGwoMeuJukfllrLdHEZxA2DyuSQAWz77dfn'),
'abcdefghijklmnop') AS STRING);
Spark
Aplicar la introspección
Consulte mediante programación las propiedades de su clúster o configuración de Databricks con SQL. Por ejemplo, puede preguntar acerca de la versión precise de su entorno Databricks SQL o Databricks Runtime. Ahora también puede usar SQL para devolver el lista de claves secretas poblado hasta ahora dentro del Servicio secreto de ladrillos de datos que el usuario precise está autorizado a ver, y solicitar a extraer valores secretos específicos por alcance y clave.
> SELECT current_version().dbsql_version;
2022.25
> SELECT current_version();
{ NULL, 2022.25, ..., ... }
> SELECT * FROM list_secrets();
scope key
------------ ---------------
secrets and techniques.r.us theAnswerToLife
> SELECT secret('secrets and techniques.r.us', 'theAnswerToLife');
fortyTwo
Construye una casa de lago geoespacial
Procese y consulte eficientemente grandes conjuntos de datos geoespaciales a escala. En esta sección, describimos las nuevas funciones SQL ahora disponibles para organizar y procesar datos de esta manera, junto con ejemplos de cómo llamar a las funciones con diferentes tipos de datos de entrada. Para obtener información más detallada, consulte el “Procesamiento de datos geoespaciales a escala con ladrillos de datos” entrada en el weblog.
A partir de hoy, Databricks admite una nueva colección de funciones geoespaciales que operan en H3 células. Cada celda H3 representa una región única del espacio en el planeta con alguna resolución, y tiene su propia ID de celda única asociada representada como una expresión BIGINT o STRING hexadecimal. Los límites de estas celdas se pueden convertir a formatos abiertos, incluidos GeoJSONun estándar diseñado para representar características geográficas simples usando JSON, o WKTun formato basado en texto abierto para expresar datos geoespaciales usando cadenas (junto con WKBsu equivalente binario).
-- This returns the middle of the enter H3 cell as a degree in GeoJSON or WKB or
-- WKT format.
> SELECT h3_centerasgeojson(599686042433355775)
{"kind":"Level","coordinates":(-121.97637597255,37.345793375368)}
Puede inspeccionar la distancia entre puntos por consultando las celdas H3 que están dentro de la distancia (cuadrícula) okay de la celda de origen. El conjunto de estas células H3 se denomina anillo okay de la célula de origen. Es posible convertir ID de celda H3 de entrada a o desde su equivalente hexadecimal representaciones de cadenas.
> SELECT h3_distance('85283447fffffff', '8528340ffffffff')
2
> SELECT h3_h3tostring(599686042433355775)
85283473fffffff
-- Returns an array of H3 cells that kind a hole hexagonal ring centered on the
-- origin H3 cell and which are at grid distance okay from the origin H3 cell.
> SELECT h3_hexring('85283473fffffff', 1) (8528340bfffffff,85283447fffffff,8528347bfffffff,85283463fffffff,85283477fffffff,8528340ffffffff)
Además, ahora puede calcular un ARRAY de ID de celdas H3 (representado como BIGINT o Instrumentos de cuerda) correspondientes a hexágonos o pentágonos contenidos en la geografía del área de entrada. El probar_ versiones devuelve NULL en lugar de generar errores.
-- It is a easy instance the place the enter is a triangle in hexadecimal WKB format.
> SELECT h3_polyfillash3(unhex('0103000000010000000400000050fc1873d79a5ec0d0d556ec2fe342404182e2c7988f5dc0f46c567dae064140aaf1d24d628052c05e4bc8073d5b444050fc1873d79a5ec0d0d556ec2fe34240'), 2)
(586146350232502271,586147449744130047,586198577034821631,586152397546455039,586199676546449407,586153497058082815,586142501941805055,586201325813891071)
Puedes calcular el padre o niño celda H3 de la celda H3 de entrada a la resolución especificada, o verifique si una celda H3 es una niño de otro. La representación de polígonos como matrices (potencialmente explotadas) de celdas H3 y puntos a través de sus celdas H3 de contención admite la realización de análisis espaciales muy eficientes que operan en las celdas H3 en lugar de los objetos geográficos originales. Además, consulte nuestro weblog reciente que describe cómo realizar análisis espacial a cualquier escala y como sobrecargue el análisis espacial usando H3.
Finalmente, puedes validar celdas H3 devolviendo el valor de entrada de tipo BIGINT o STRING si corresponde a una ID de celda H3 válida.
> SELECT h3_toparent('85283473fffffff', 0)
8029fffffffffff
> SELECT h3_tochildren(599686042433355775, 6)
(604189641121202175,604189641255419903,604189641389637631,604189641523855359,604189641658073087,604189641792290815,604189641926508543)
> SELECT h3_ischildof(608693241318998015, 599686042433355775)
true
> SELECT h3_validate(599686042433355776)
(H3_INVALID_CELL_ID) 599686042433355776 is not a legitimate H3 cell ID
> SELECT h3_isvalid(599686042433355776)
false
> SELECT h3_try_validate(599686042433355776)
NULL
Databricks SQL le permite hacer cualquier cosa
El cumplimiento de estándares y la fácil migración llegaron a Databricks SQL anteriormente con el nacimiento de Modo ANSIy ya establece el récord mundial en rendimiento. Con la adición de esta amplia gama de nuevas funciones integradas, las cargas de trabajo de SQL ahora tienen una capacidad de expresión nueva y significativa en Lakehouse.
Ahora siéntase libre de cortar cadenas, agregar valores, manipular fechas, analizar geografías, y más. Y si falta alguna funcionalidad en estos integrados, echa un vistazo Funciones definidas por el usuario de Python y Funciones definidas por el usuario de SQL para definir su propia lógica que se comporte de la misma manera en los sitios de llamadas que los integrados.
¡Gracias por usar Databricks SQL y feliz consulta!
