CockroachDB: La guía definitiva

Capítulo 4. CockroachDB SQL

Este trabajo se ha traducido utilizando IA. Agradecemos tus opiniones y comentarios: [e-mail]

El lenguaje de CockroachDB es SQL. Aunque existen algunas utilidades de línea de comandos, todas las interacciones entre una aplicación y la base de datos están mediadas por comandos del lenguaje SQL.

SQL es un lenguaje rico y con una larga historia, de la que ya hablamos en el Capítulo 1. Una definición completa de todas las características del lenguaje SQL requeriría un libro por derecho propio y quedaría desfasada casi instantáneamente, porque el lenguaje SQL evoluciona con cada versión.

Por lo tanto, este capítulo pretende proporcionarte una amplia visión general del lenguaje SQL utilizado en CockroachDB sin pretender ser una referencia completa. Adoptaremos un enfoque de SQL orientado a las tareas, cubriendo las tareas más comunes del lenguaje SQL con especial referencia a las características únicas de la implementación SQL de CockroachDB.

Como describimos en el Capítulo 1, SQL es un lenguaje declarativo. Las sentencias SQL representan peticiones lógicas para consultas y operaciones de manipulación de datos sin especificar cómo debe implementar la base de datos esas peticiones.

Puedes encontrar una referencia completa del lenguaje SQL de CockroachDB en el conjunto de documentación de CockroachDB. Puedes encontrar una revisión más amplia del lenguaje SQL en el libro de O'Reilly SQL in a Nutshell.

Algunos de los ejemplos de este capítulo utilizan el conjunto de datos de muestra MovR de este capítulo para ilustrar diversas funciones del lenguaje SQL. En el Capítulo 2 mostramos cómo instalar los datos de muestra.

Compatibilidad con el lenguaje SQL

CockroachDB es ampliamente compatible con , la implementación PostgreSQL de la norma SQL:2016. El estándar SQL:2016 contiene varios módulos independientes, y ninguna base de datos importante implementa todos los estándares. Sin embargo, la implementación PostgreSQL de SQL es posiblemente lo más parecido a un "estándar" que existe en la comunidad de bases de datos.

CockroachDB varía de PostgreSQL en un par de aspectos:

• Actualmente, CockroachDB no admite procedimientos almacenados, eventos ni desencadenadores. En PostgreSQL, estos procedimientos almacenados se escriben en el lenguaje PL/pgSQL y permiten la ejecución de lógica de programa dentro del servidor de base de datos, ya sea bajo demanda o en respuesta a algún evento desencadenante.

• CockroachDB no admite actualmente funciones definidas por el usuario.

• CockroachDB no es compatible con las funciones XML de PostgreSQL.

• CockroachDB no es compatible con los índices y funciones de texto completo de PostgreSQL.

Consulta de datos con SELECT

Aunque necesitamos crear y rellenar las tablas antes de consultarlas, es lógico empezar con la sentencia SELECT, ya que muchas características de la sentencia SELECT aparecen en otros tipos de SQL -subconsultas en UPDATEs, por ejemplo- y para los científicos y analistas de datos, la sentencia SELECT es a menudo la única sentencia SQL que necesitan aprender.

La sentencia SELECT (Figura 4-1) es el caballo de batalla de las consultas relacionales y tiene una sintaxis compleja y rica. La sentencia SELECT de CockroachDB implementa las características estándar de la sentencia estándar SELECT, con sólo unas pocas características específicas de CockroachDB.

Figura 4-1. DeclaraciónSELECT

En los siguientes apartados, examinaremos cada uno de los elementos principales de la declaración SELECT, así como las funciones y operadores que pueden incluirse en ella.

La lista SELECCIONAR

Una sentencia SQL simple consiste en nada más que una sentencia SELECT junto con expresiones escalares (por ejemplo, expresiones que devuelven un único valor). Por ejemplo

SELECT CONCAT('Hello from CockroachDB at ',
              CAST (NOW() as STRING)) as hello;

La lista SELECT incluye una lista de expresiones separadas por comas que pueden contener combinaciones de constantes, funciones y operadores. El lenguaje SQL de CockroachDB admite todos los operadores SQL conocidos. Puedes encontrar una lista completa de funciones y operadores en el conjunto de documentación de CockroachDB.

La cláusula FROM

La cláusula FROM es el método principal de adjuntar datos de la tabla a la sentencia SELECT. En su forma más sencilla, todas las filas y columnas de una tabla pueden obtenerse mediante un escaneo completo de la tabla:

SELECT * FROM rides;

Los nombres de tabla pueden tener un alias utilizando la cláusula AS o simplemente poniendo un alias a continuación del nombre de la tabla. Ese alias se puede utilizar en cualquier parte de la consulta para hacer referencia a la tabla. Los nombres de columna también pueden tener alias. Por ejemplo, los siguientes son todos equivalentes:

SELECT name FROM users;
SELECT u.name FROM users u;
SELECT users.name FROM users;
SELECT users.name AS user_name FROM users;
SELECT u.name FROM users AS u;

ÚNETE A

Las uniones permiten fusionar los resultados de dos o más tablas basándose en algunos valores de columna comunes.

La INNER JOIN es la operación por defecto JOIN. En esta unión , las filas de una tabla se unen a filas de otra tabla basándose en algunos valores comunes ("clave"). Las filas que no coinciden en ambas tablas no se incluyen en los resultados. Por ejemplo, la siguiente consulta une la información de vehículos y viajes en la base de datos movr:

SELECT v.id,v.ext,r.start_time, r.start_address
  FROM vehicles v
  INNER JOIN rides r
    ON (r.vehicle_id=v.id);

Ten en cuenta que un vehículo que no hubiera estado implicado en una carrera no se incluiría en el conjunto de resultados.

La cláusula ON especifica las condiciones que unen las dos tablas: en la consulta anterior, las columnas vehicle_id de la tabla rider se emparejaron con la columna id de la tabla vehicles. Si JOIN se encuentra en una columna de nombre idéntico en ambas tablas, la cláusula USING proporciona un atajo práctico. Aquí unimos users y user_ride_counts utilizando la columna común name:¹

SELECT *
  FROM users u
  JOIN user_ride_counts urc
 USING (name);

La tabla OUTER JOIN permite incluir filas aunque no tengan ninguna coincidencia en la otra tabla. Las filas que no se encuentran en la tabla OUTER JOIN se representan mediante los valores NULL. LEFT y RIGHT determinan en qué tabla pueden faltar valores. Por ejemplo, la siguiente consulta imprime todos los usuarios de la tabla users, aunque algunos no estén asociados a un código promocional:

SELECT u.name , upc.code
  FROM users u
  LEFT OUTER JOIN user_promo_codes upc
    ON (u.id=upc.user_id);

El RIGHT OUTER JOIN invierte el valor por defecto (LEFT) OUTER JOIN . Por tanto, esta consulta es idéntica a la anterior porque la tabla users es ahora la tabla "correcta" en la unión:

SELECT DISTINCT u.name , upc.code
  FROM user_promo_codes upc
  RIGHT OUTER JOIN users u
    ON (u.id=upc.user_id);

Anti Juntas

A menudo es necesario seleccionar todas las filas de una tabla que no tengan una fila coincidente en algún otro conjunto de resultados. Esto se denomina anti-unión, y aunque no existe una sintaxis SQL para este concepto, se suele implementar utilizando una subconsulta y la cláusula IN o EXISTS. El siguiente ejemplo ilustra una antiunión utilizando los operadores EXISTS y IN.

Cada ejemplo selecciona usuarios que no son también empleados:

SELECT *
  FROM users
 WHERE id NOT IN
       (SELECT id FROM employees);

Esta consulta devuelve los mismos resultados pero utilizando una subconsulta correlacionada (hablaremos de las subconsultas con más detalle en una próxima sección):

SELECT *
   FROM users u
  WHERE NOT EXISTS
        (SELECT id
           FROM employees e
          WHERE e.id=u.id);

Uniones en cruz

CROSS JOIN indica que cada fila de la tabla izquierda debe unirse a cada fila de la tabla derecha. Normalmente, esto es una receta para el desastre, a menos que una de las tablas sólo tenga una fila o sea una subconsulta correlacionada lateralmente (consulta "Subconsulta correlacionada").

Operaciones de Conjunto

SQL implementa una serie de operaciones que tratan directamente con conjuntos de resultados. Estas operaciones, denominadas colectivamente "operaciones de conjunto", permiten concatenar, restar o superponer conjuntos de resultados.

La más común de estas operaciones es el operador UNION, que devuelve la suma de dos conjuntos de resultados. Por defecto, se eliminan los duplicados de cada conjunto de resultados. En cambio, la operación UNION ALL devuelve la suma de los dos conjuntos de resultados, incluidos los duplicados. El siguiente ejemplo devuelve una lista de clientes y empleados. Los empleados que también sean clientes sólo aparecerán en la lista una vez:

SELECT name, address
  FROM customers
 UNION
SELECT name,address
  FROM employees;

INTERSECT devuelve las filas que están en ambos conjuntos de resultados. Esta consulta devuelve los clientes que también son empleados:

SELECT name, address
  FROM customers
 INTERSECT
SELECT name,address
  FROM employees;

EXCEPT devuelve filas del primer conjunto de resultados que no están presentes en el segundo. Esta consulta devuelve clientes que no son también empleados:

SELECT name, address
  FROM customers
 EXCEPT
SELECT name,address
  FROM employees;

Todas las operaciones de conjunto requieren que las consultas componentes devuelvan el mismo número de columnas y que esas columnas sean de un tipo de datos compatible con .

Operaciones del Grupo

Las operaciones de agregación permiten generar información resumida, normalmente a partir de agrupaciones de filas. Las filas pueden agruparse utilizando el operador GROUP BY . Si se hace esto, la lista de selección debe estar formada sólo por columnas contenidas en la cláusula GROUP BY y las funciones agregadas.

Las funciones agregadas más habituales se muestran en la Tabla 4-1.

Tabla 4-1. Funciones agregadas

AVG	Calcula el valor medio del grupo.
COUNT	Devuelve el número de filas del grupo.
MAX	Devuelve el valor máximo del grupo.
MIN	Devuelve el valor mínimo del grupo.
STDDEV	Devuelve la desviación típica del grupo.
SUM	Devuelve el total de todos los valores del grupo.

El siguiente ejemplo genera información resumida sobre el viaje para cada ciudad:

SELECT u.city,SUM(urc.rides),AVG(urc.rides),max(urc.rides)
  FROM users u
  JOIN user_ride_counts urc
 USING (name)
 GROUP BY u.city;

Subconsultas

Una subconsulta es una sentencia SELECT que se produce dentro de otra sentencia SQL. Una sentencia SELECT "anidada" de este tipo puede utilizarse en una amplia variedad de contextos SQL, incluidas las sentencias SELECT, DELETE, UPDATE y INSERT.

La siguiente sentencia utiliza una subconsulta para contar el número de viajes que comparten la duración máxima del viaje:

SELECT COUNT(*) FROM rides
 WHERE (end_time-start_time)=
 	(SELECT MAX(end_time-start_time) FROM rides );

También se pueden utilizar subconsultas en la cláusula FROM siempre que pueda aparecer una definición de tabla o vista. Esta consulta genera un resultado que compara cada trayecto con la duración media del trayecto en la ciudad:

SELECT id, city,(end_time-start_time) ride_duration, avg_ride_duration
  FROM rides
  JOIN (SELECT city,
               AVG(end_time-start_time) avg_ride_duration
	   FROM rides
	  GROUP BY city)
 USING(city) ;

Subconsulta correlacionada

Una subconsulta correlacionada es una en la que la subconsulta hace referencia a valores de la consulta u operación padre. La subconsulta devuelve un resultado potencialmente distinto para cada fila del conjunto de resultados padre. Hemos visto un ejemplo de subconsulta correlacionada al realizar una "antiunión" anteriormente en el capítulo.

SELECT *
   FROM users u
  WHERE NOT EXISTS
        (SELECT id
           FROM employees e
          WHERE e.id=u.id);

Las subconsultas pueden utilizarse a menudo para realizar una operación que es funcionalmente equivalente a una unión. En muchos casos, el optimizador de consultas transformará estas sentencias en uniones para agilizar el proceso de optimización.

Subconsulta lateral

Cuando se utiliza una subconsulta en una unión, la palabra clave LATERAL indica que la subconsulta puede acceder a columnas generadas en expresiones de tablas FROM precedentes. Por ejemplo, en la siguiente consulta, la palabra clave LATERAL permite que la subconsulta acceda a columnas de la tabla users:

  SELECT name, address, start_time
   FROM users CROSS JOIN
        LATERAL (SELECT *
                   FROM rides
                  WHERE rides.start_address = users.address ) r;

Este ejemplo es un poco artificioso y, evidentemente, podríamos construir un sencillo JOIN que realizara esta consulta de forma más natural. Donde realmente brillan las uniones LATERAL es en permitir que las subconsultas accedan a columnas calculadas en otras subconsultas dentro de una cláusula FROM. La entrada del blog CockroachDB de Andy Woods describe un ejemplo más serio de subconsultas laterales.

La cláusula WHERE

La cláusula WHERE es común a las sentencias SELECT, UPDATE y DELETE. Especifica un conjunto de condiciones lógicas que deben evaluarse a true para que todas las filas sean devueltas o procesadas por la sentencia SQL en cuestión.

Expresiones comunes de la tabla

Las sentencias SQL con muchas subconsultas pueden ser difíciles de leer y mantener, sobre todo si la misma subconsulta se necesita en varios contextos dentro de la consulta. Por esta razón, SQL admite Expresiones de Tabla Comunes utilizando la cláusula WITH. La Figura 4-2 muestra la sintaxis de una Expresión Común de Tabla.

Figura 4-2. Expresión de tabla común

En su forma más simple, una Expresión Común de Tabla es simplemente un bloque de consulta con nombre que puede aplicarse siempre que pueda utilizarse una expresión de tabla. Por ejemplo, aquí utilizamos la cláusula WITH para crear una Expresión Común de Tabla, riderRevenue, y luego hacemos referencia a ella en la cláusula FROM de la consulta principal:

WITH riderRevenue AS (
	  SELECT u.id, SUM(r.revenue) AS sumRevenue
	    FROM rides r JOIN "users" u
	    ON (r.rider_id=u.id)
	   GROUP BY u.id)
SELECT * FROM "users" u2
         JOIN riderRevenue rr USING (id)
 ORDER BY sumrevenue DESC;

La cláusula RECURSIVE permite que la Expresión Común de Tabla se refiera a sí misma, permitiendo potencialmente que una consulta devuelva un conjunto arbitrariamente alto (o incluso infinito) de resultados. Por ejemplo, si la tabla employees contuviera una columna manager_id que hiciera referencia a la fila del gerente en la misma tabla, entonces podríamos imprimir una jerarquía de empleados y gerentes de la siguiente manera:

WITH RECURSIVE employeeMgr AS (
  SELECT id,manager_id, name , NULL AS manager_name, 1 AS level
    FROM employees managers
   WHERE manager_id IS NULL
  UNION ALL
  SELECT subordinates.id,subordinates.manager_id,
         subordinates.name, managers.name ,managers.LEVEL+1
    FROM employeeMgr managers
    JOIN employees subordinates
      ON (subordinates.manager_id=managers.id)
)
SELECT * FROM employeeMgr;

La cláusula MATERIALIZED obliga a CockroachDB a almacenar los resultados de la Expresión de Tabla Común como una tabla temporal en lugar de volver a ejecutarla en cada ocurrencia. Esto puede ser útil si se hace referencia a la Expresión de Tabla Común varias veces en la consulta.

ORDENAR POR

La cláusula ORDER BY permite que los resultados de la consulta se devuelvan ordenados. La Figura 4-3 muestra la sintaxis ORDER BY .

Figura 4-3. ORDER BY

De la forma más sencilla, ORDER BY toma una o varias expresiones de columna o números de columna de la lista SELECT.

En este ejemplo, ordenamos por números de columna:

SELECT city,start_time, (end_time-start_time) duration
   FROM rides r
  ORDER BY 1,3 DESC;

Y en este caso, por expresiones de columna:

SELECT city,start_time, (end_time-start_time) duration
   FROM rides r
  ORDER BY city,(end_time-start_time)  DESC;

También puedes ordenar por un índice. En el siguiente ejemplo, las filas se ordenarán por city y start_time, ya que son las columnas especificadas en el índice:

CREATE INDEX rides_start_time ON rides (city ,start_time);

SELECT city,start_time, (end_time-start_time) duration
  FROM rides
 ORDER BY INDEX rides@rides_start_time;

El uso de ORDER BY INDEX garantiza que el índice se utilizará para devolver directamente las filas ordenadas, en lugar de tener que realizar una operación de ordenación en las filas después de recuperarlas. Consulta el Capítulo 8 para obtener más consejos sobre la optimización de sentencias que contengan un ORDER BY .

Funciones de ventana

Las funciones ventana son funciones que operan sobre un subconjunto -una "ventana"- del conjunto completo de los resultados. La Figura 4-4 muestra la sintaxis de una función ventana.

Figura 4-4. Sintaxis de la función Ventana

PARTITION BY y ORDER BY crean una especie de "tabla virtual" con la que trabaja la función. Por ejemplo, esta consulta enumera los 10 viajes más importantes en términos de ingresos, mostrando el porcentaje de los ingresos totales y los ingresos de la ciudad:

SELECT city, r.start_time ,revenue,
       revenue*100/SUM(revenue) OVER () AS pct_total_revenue,
       revenue*100/SUM(revenue) OVER (PARTITION BY city) AS pct_city_revenue
  FROM rides r
 ORDER BY 5 DESC
 LIMIT 10;

Hay algunas funciones de agregación que son específicas de las funciones de ventana. RANK() clasifica la fila existente dentro de la ventana correspondiente, y DENSE_RANK() hace lo mismo sin permitir clasificaciones "perdidas". LEAD y LAG proporcionan acceso a funciones en particiones adyacentes.

Por ejemplo, esta consulta devuelve los 10 mejores trayectos, mostrando la clasificación general de cada trayecto y su clasificación dentro de la ciudad :

SELECT city, r.start_time ,revenue,
       RANK() OVER
           (ORDER BY revenue DESC) AS total_revenue_rank,
       RANK() OVER
            (PARTITION BY city ORDER BY revenue DESC) AS city_revenue_rank
  FROM rides r
 ORDER BY revenue DESC
 LIMIT 10;

Otras cláusulas SELECT

La cláusula LIMIT limita el número de filas devueltas por un SELECT mientras que la cláusula OFFSET "salta hacia delante" un determinado número de filas. Esto puede ser útil para paginar a través de un conjunto de resultados, aunque casi siempre es más eficaz utilizar una condición de filtro para navegar al siguiente subconjunto de resultados porque, de lo contrario, cada petición tendrá que releer y descartar un número creciente de filas.

Matrices CucarachaDB

El tipo ARRAY permite definir una columna como una matriz unidimensional de elementos, cada uno de los cuales comparte un tipo de datos común. Hablaremos de las matrices en el contexto del modelado de datos en el próximo capítulo. Aunque pueden ser útiles, en sentido estricto constituyen una violación del modelo relacional y deben utilizarse con cuidado.

Una variable ARRAY se define añadiendo "[]" o la palabra "ARRAY" al tipo de datos de una columna. Por ejemplo

CREATE TABLE arrayTable (arrayColumn STRING[]);
CREATE TABLE anotherTable (integerArray INT ARRAY);

La función ARRAY nos permite insertar varios elementos en ARRAY:

INSERT INTO arrayTable VALUES (ARRAY['sky', 'road', 'car']);
SELECT * FROM arrayTable;

   arraycolumn
------------------
  {sky,road,car}

Podemos acceder a un elemento individual de una matriz con la siguiente notación familiar de elementos de matriz:

SELECT arrayColumn[2] FROM arrayTable;
  arraycolumn
---------------
  road

El operador @> puede utilizarse para buscar matrices que contengan uno o varios elementos:

SELECT * FROM arrayTable WHERE arrayColumn @>ARRAY['road'];
   arraycolumn
------------------
  {sky,road,car}

Podemos añadir elementos a una matriz existente utilizando la función array_append y eliminar elementos utilizando array_remove:

UPDATE  arrayTable
   SET arrayColumn=array_append(arrayColumn,'cat')
  WHERE arrayColumn @>ARRAY['car']
 RETURNING arrayColumn;

     arraycolumn
----------------------
  {sky,road,car,cat}

 UPDATE  arrayTable
   SET arrayColumn=array_remove(arrayColumn,'car')
  WHERE arrayColumn @>ARRAY['car']
  RETURNING arrayColumn;

   arraycolumn
------------------
  {sky,road,cat}

Por último, la función unnest transforma una matriz en un resultado tabular: una fila por cada elemento de la matriz. Esto se puede utilizar para "unir" el contenido de una matriz con datos que se mantienen en forma relacional en otro lugar de la base de datos. Mostraremos un ejemplo en el capítulo siguiente:

SELECT unnest(arrayColumn)
  FROM ((("queries", "arrays", startref="qarys")))arrayTable;

  unnest
----------
  sky
  road
  cat

Trabajar con JSON

El tipo de datos JSONB nos permite almacenar documentos JSON en una columna, y CockroachDB proporciona operadores y funciones para ayudarnos a trabajar con JSON.

Para estos ejemplos, hemos creado una tabla con una clave primaria customerid y todos los datos en una columna JSONB, jsondata. Podemos utilizar la función jsonb_pretty para recuperar el JSON con un formato agradable:

 SELECT jsonb_pretty(jsondata)
   FROM customersjson WHERE customerid=1;

                     jsonb_pretty
------------------------------------------------------
  {
      "Address": "1913 Hanoi Way",
      "City": "Sasebo",
      "Country": "Japan",
      "District": "Nagasaki",
      "FirstName": "MARY",
      "LastName": "Smith",
      "Phone": 886780309,
      "_id": "5a0518aa5a4e1c8bf9a53761",
      "dateOfBirth": "1982-02-20T13:00:00.000Z",
      "dob": "1982-02-20T13:00:00.000Z",
      "randValue": 0.47025846594884335,
      "views": [
          {
              "filmId": 611,
              "title": "MUSKETEERS WAIT",
              "viewDate": "2013-03-02T05:26:17.645Z"
          },
          {
              "filmId": 308,
              "title": "FERRIS MOTHER",
              "viewDate": "2015-07-05T20:06:58.891Z"
          },
          {
              "filmId": 159,
              "title": "CLOSER BANG",
              "viewDate": "2012-08-04T19:31:51.698Z"
          },
          /* Some data removed */
      ]
  }

Cada documento JSON contiene algunos atributos de nivel superior y una matriz anidada de documentos que contiene detalles de las películas que han retransmitido.

Podemos hacer referencia a atributos JSON específicos en la cláusula SELECT utilizando el operador ->:

 SELECT jsondata->'City' AS City
  FROM customersjson WHERE customerid=1;

    city
------------
  "Sasebo"

El operador ->> es similar, pero devuelve los datos formateados como texto, no como JSON.

Si queremos buscar dentro de una columna JSONB, podemos utilizar el operador @>:

SELECT COUNT(*) FROM customersjson
 WHERE jsondata @> '{"City": "London"}';

  count
---------
      3

Podemos obtener el mismo resultado utilizando el operador ->>:

SELECT COUNT(*) FROM customersjson
 WHERE jsondata->>'City' = 'London';

  count
---------
      3

Los operadores ->> y @> pueden tener características de rendimiento diferentes. En concreto, ->> podría explotar un índice invertido, mientras que @> utilizaría un escaneo de tabla.

Podemos interrogar la estructura del documento JSON utilizando las funciones jsonb_each y jsonb_object_keys. jsonb_each devuelve una fila por atributo en el documento JSON, mientras que jsonb_object_keys devuelve sólo las claves de los atributos. Esto es útil si no sabes qué se almacena dentro de la columna JSONB.

jsonb_array_elements devuelve una fila por cada elemento de una matriz JSON. Por ejemplo, aquí ampliamos la matriz views para un cliente concreto, contando el número de películas que ha visto:

SELECT COUNT(jsonb_array_elements(jsondata->'views'))
  FROM customersjson
 WHERE customerid =1;

  count
---------
     37
(1 row)

Resumen de SELECT

La sentencia SELECT es probablemente la más utilizada en la programación de bases de datos y ofrece una amplia gama de funcionalidades. Incluso después de décadas trabajando en este campo, nosotros tres no conocemos todos los matices de la funcionalidad de SELECT. Sin embargo, aquí hemos intentado proporcionarte los aspectos más importantes del lenguaje. Para profundizar más, consulta el conjunto de documentación de CockroachDB.

Aunque algunos profesionales de las bases de datos utilizan SELECT casi exclusivamente, la mayoría también creará y manipulará datos. En las secciones siguientes, veremos las características del lenguaje que dan soporte a esas actividades.

Crear tablas e índices

En una base de datos relacional, sólo se pueden añadir datos a tablas predefinidas. Estas tablas se crean mediante la sentencia CREATE TABLE . Se pueden crear índices para aplicar restricciones únicas o para proporcionar una ruta de acceso rápido a los datos. Los índices pueden definirse dentro de la sentencia CREATE TABLE o mediante una sentencia independiente CREATE INDEX .

La estructura del esquema de una base de datos constituye una limitación crítica para el rendimiento de la base de datos y también para su mantenimiento y utilidad. Trataremos las consideraciones clave para el diseño de la base de datos en el Capítulo 5. De momento, vamos a crear unas cuantas tablas sencillas.

Utilizamos CREATE TABLE para crear una tabla dentro de una base de datos. La Figura 4-5 muestra una sintaxis simplificada de la sentencia CREATE TABLE .

Figura 4-5. CREATE TABLE declaración

En el siguiente ejemplo se muestra un sencillo CREATE TABLE . Crea una tabla, mytable, con una única columna, mycolumn. La columna mycolumn sólo puede almacenar valores enteros:

CREATE TABLE mytable
(
      mycolumn int
);

La sentencia CREATE TABLE debe definir las columnas que aparecen en la tabla y, opcionalmente, puede definir índices, familias de columnas, restricciones y particiones asociadas a la tabla. Por ejemplo, la sentencia CREATE TABLE para la tabla rides de la base de datos movr tendría el siguiente aspecto:

CREATE TABLE public.rides (
	id UUID NOT NULL,
	city VARCHAR NOT NULL,
	vehicle_city VARCHAR NULL,
	rider_id UUID NULL,
	vehicle_id UUID NULL,
	start_address VARCHAR NULL,
	end_address VARCHAR NULL,
	start_time TIMESTAMP NULL,
	end_time TIMESTAMP NULL,
	revenue DECIMAL(10,2) NULL,
	CONSTRAINT "primary" PRIMARY KEY (city ASC, id ASC),
	CONSTRAINT fk_city_ref_users
	   FOREIGN KEY (city, rider_id)
	   REFERENCES public.users(city, id),
	CONSTRAINT fk_vehicle_city_ref_vehicles
	   FOREIGN KEY (vehicle_city, vehicle_id)
	   REFERENCES public.vehicles(city, id),
	INDEX rides_auto_index_fk_city_ref_users
	   (city ASC, rider_id ASC),
	INDEX rides_auto_index_fk_vehicle_city_ref_vehicles
          (vehicle_city ASC, vehicle_id ASC),
	CONSTRAINT check_vehicle_city_city
           CHECK (vehicle_city = city)
);

Esta declaración CREATE TABLE especifica columnas adicionales, su anulabilidad, claves primarias y foráneas, índices y restricciones sobre los valores de la tabla.

Las cláusulas relevantes de la Figura 4-5 se enumeran en la Tabla 4-2.

Tabla 4-2. Opciones deCREATE TABLE

column_def	La definición de una columna. Incluye el nombre de la columna, el tipo de datos y la anulabilidad. También se pueden incluir aquí las restricciones específicas de la columna, aunque es mejor enumerar todas las restricciones por separado.
index_def	Definición de un índice que se creará en la tabla. Igual que CREATE INDEX pero sin el verbo inicial CREATE.
table_constraint	Una restricción sobre la tabla, como PRIMARY KEY , FOREIGN KEY , o CHECK. Consulta "Restricciones" para ver la sintaxis de las restricciones.
family_def	Asigna columnas a una familia de columnas. Consulta el Capítulo 2 para obtener más información sobre las familias de columnas.

Veamos ahora cada una de estas opciones de CREATE TABLE .

Definiciones de columna

Una definición de columna consiste en un nombre de columna, un tipo de datos, un estado de anulabilidad, un valor por defecto y, posiblemente, definiciones de restricciones a nivel de columna. Como mínimo, deben especificarse el nombre y el tipo de datos. La Figura 4-6 muestra la sintaxis de una definición de columna.

Figura 4-6. Definición de columna

Aunque las restricciones pueden especificarse directamente contra las definiciones de columna, también pueden enumerarse independientemente debajo de las definiciones de columna. Muchos profesionales prefieren enumerar las restricciones por separado de esta forma porque permite localizar juntas todas las restricciones, incluidas las de varias columnas.

Columnas calculadas

CockroachDB permite que las tablas incluyan columnas computadas que en otras bases de datos requerirían la definición de una vista:

column_name AS expression [STORED|VIRTUAL]

Una columna VIRTUAL calculada se evalúa siempre que se hace referencia a ella. Una expresión STORED se almacena en la base de datos cuando se crea y no es necesariovolver a calcularla siempre.

Por ejemplo, esta definición de tabla tiene las columnas firstName y lastName concatenadas en una columna fullName:

CREATE TABLE people
 (
     id INT PRIMARY KEY,
     firstName VARCHAR NOT NULL,
     lastName VARCHAR NOT NULL,
     dateOfBirth DATE NOT NULL,
     fullName STRING AS (CONCAT(firstName,' ',lastName) ) STORED

 );

Las columnas calculadas no pueden depender del contexto. Es decir, el valor calculado no debe cambiar con el tiempo ni ser de otro modo no determinista. Por ejemplo, la columna computada del siguiente ejemplo no funcionaría, ya que la columna age sería estática en lugar de recalcularse cada vez. Aunque en la vida real estaría bien dejar de envejecer, probablemente queramos que la columna age aumente a medida que pasa el tiempo.

CREATE TABLE people
 (
     id INT PRIMARY KEY,
     firstName VARCHAR NOT NULL,
     lastName VARCHAR NOT NULL,
     dateOfBirth timestamp NOT NULL,
     fullName STRING AS (CONCAT(firstName,' ',lastName) ) STORED,
     age int AS (now()-dateOfBirth) STORED
 );

Tipos de datos

Los tipos de datos base CockroachDB se muestran en la Tabla 4-3.

Tabla 4-3. Tipos de datos de CockroachDB

Type	Descripción	Ejemplo
ARRAY	Una matriz homogénea, unidimensional y con 1 índice, de cualquier tipo de datos que no sea matriz.	{"cielo", "carretera", "coche"}
BIT	Una cadena de dígitos binarios (bits).	B'10010101'
BOOL	Un valor booleano.	verdadero
BYTES	Una cadena de caracteres binarios.	b’\141\061\142\062\143\063’
COLLATE	La función COLLATE te permite ordenar los valores de STRING según reglas específicas de cada idioma y país, conocidas como cotejos.	a1b2c3 COLLATE es
DATE	Una cita.	FECHA 2016-01-25
ENUM	Nuevo en v20.2: Un tipo de datos definido por el usuario compuesto por un conjunto de valores estáticos.	ENUM(trébol, diamante, corazón, pica)
DECIMAL	Un número exacto de punto fijo.	1.2345
FLOAT	Un número de 64 bits, inexacto, de coma flotante.	3.141592653589793
INET	Una dirección IPv4 o IPv6.	192.168.0.1
INT	Un entero con signo, de hasta 64 bits.	12345
INTERVAL	Un lapso de tiempo.	INTERVALO 2h30m30s
JSONB	Datos JSON.	{"nombre": "Lola", "apellido": "Perro", "ubicación": "NYC", "online" : true, "amigos" : 547}
SERIAL	Un pseudotipo que crea números ascendentes únicos.	148591304110702593
STRING	Una cadena de caracteres Unicode.	a1b2c3
TIME TIMETZ	TIME almacena una hora del día en UTC. TIMETZ convierte los valores de TIME con un desfase horario especificado respecto a UTC.	TIME 01:23:45.123456 TIMETZ 01:23:45.123456-5:00
TIMESTAMP TIMESTAMPTZ	TIMESTAMP almacena un emparejamiento de fecha y hora en UTC.TIMESTAMPTZ convierte los valores de TIMESTAMP con un desfase horario especificado respecto a UTC.	TIMESTAMP 2016-01-25 10:10:10TIMESTAMPTZ 2016-01-25 10:10:10-05:00
UUID	Un valor hexadecimal de 128 bits.	7f9c24e8-3b12-4fef-91e0-56a2d5a246ec

Ten en cuenta que otros nombres de tipos de datos pueden tener alias contra estos tipos base de CockroachDB. Por ejemplo, los tipos de PostgreSQL BIGINT y SMALLINT tienen un alias contra el tipo de CockroachDB INT.

En CockroachDB, los tipos de datos se pueden fundir -o convertir- añadiendo el tipo de dato a una expresión mediante "::". Por ejemplo:

SELECT revenue::int FROM rides;

La función CAST también puede utilizarse para convertir tipos de datos y es más ampliamente compatible con otras bases de datos y normas SQL. Por ejemplo:

SELECT CAST(revenue AS int) FROM rides;

Claves primarias

Como sabemos, una clave primaria define de forma única una fila dentro de una tabla. En CockroachDB, una clave primaria es obligatoria porque todas las tablas se distribuyen por el clúster en función de los rangos de su clave primaria. Si no especificas una clave primaria, se generará automáticamente una clave por ti.

Es una práctica habitual en otras bases de datos definir una clave primaria autogenerada mediante cláusulas como AUTOINCREMENT. La generación de claves primarias en bases de datos distribuidas es una cuestión importante, porque es la clave primaria la que se utiliza para distribuir los datos entre los nodos del clúster. Discutiremos las opciones para la generación de claves primarias en el próximo capítulo, pero por ahora, nos limitaremos a señalar que puedes generar valores de clave primaria aleatorios utilizando el tipo de datos UUID con la función gen_random_uuid() como valor por defecto:

CREATE TABLE people (
        id UUID NOT NULL DEFAULT gen_random_uuid(),
        firstName VARCHAR NOT NULL,
        lastName VARCHAR NOT NULL,
        dateOfBirth DATE NOT NULL
 );

Este patrón se considera la mejor práctica para garantizar una distribución uniforme de las claves en todo el clúster. En el Capítulo 5 se tratarán otras opciones para autogenerar claves primarias.

Restricciones

La cláusula CONSTRAINT especifica condiciones que deben cumplir todas las filas de una tabla. En algunas circunstancias, la palabra clave CONSTRAINT puede omitirse, por ejemplo, al definir una restricción de columna o tipos de restricción específicos como PRIMARY KEY o FOREIGN KEY . La Figura 4-7 muestra la forma general de una definición de restricción.

Figura 4-7. DeclaraciónCONSTRAINT

Una restricción UNIQUE requiere que todos los valores de para column o column_list sean únicos.

PRIMARY KEY implementa un conjunto de columnas que deben ser únicas y que también pueden ser objeto de una restricción FOREIGN KEY en otra tabla. Tanto las restricciones PRIMARY KEY como UNIQUE requieren la creación de un índice implícito. Si se desea, las características físicas de almacenamiento del índice pueden especificarse en la cláusula USING. Las opciones de la cláusula USING INDEX tienen los mismos usos que en la sentencia CREATE INDEX .

NOT NULL indica que la columna en cuestión no puede ser NULL. Esta opción sólo está disponible para las restricciones de columna, pero se puede obtener el mismo efecto con una restricción de tabla CHECK.

CHECK define una expresión que debe evaluarse en true para cada fila de la tabla. Trataremos las buenas prácticas para crear restricciones en el Capítulo 5.

Un uso sensato de las restricciones puede ayudar a garantizar la calidad de los datos y dotar a la base de datos de cierto grado de autodocumentación. Sin embargo, algunas restricciones tienen importantes implicaciones para el rendimiento; hablaremos de ellas en el Capítulo 5.

Índices

Los índices se pueden crear mediante la sentencia CREATE INDEX o se puede incluir una definición INDEX dentro de la sentencia CREATE TABLE .

Hablamos mucho de los índices en el Capítulo 2, y seguiremos hablando de ellos en los capítulos sobre diseño de esquemas y ajuste del rendimiento (Capítulos 5 y 8, respectivamente). Una indexación eficaz es uno de los factores de éxito más importantes para una implementación de CockroachDB de alto rendimiento.

La Figura 4-8 ilustra una sintaxis simplista para lasentencia CockroachDB CREATE INDEX .

Figura 4-8. CREATE INDEX declaración

En el Capítulo 2 vimos el funcionamiento interno de los índices de CockroachDB. Desde el punto de vista del rendimiento, los índices de CockroachDB se comportan de forma muy similar a los índices de otras bases de datos: proporcionan un método de acceso rápido para localizar filas con un conjunto concreto de valores clave no primarios. Por ejemplo, si simplemente queremos localizar una fila con un nombre y una fecha de nacimiento concretos, podríamos crear el siguiente índice multicolumna:

CREATE INDEX people_namedob_ix ON people
 (lastName,firstName,dateOfBirth);

Si quisiéramos asegurarnos aún más de que no puede haber dos filas con el mismo valor para el nombre y la fecha de nacimiento, podríamos crear un índice único:

CREATE UNIQUE INDEX people_namedob_ix ON people
 (lastName,firstName,dateOfBirth);

La cláusula STORING nos permite almacenar datos adicionales en el índice, lo que puede permitirnos satisfacer consultas utilizando sólo el índice. Por ejemplo, este índice puede satisfacer consultas que recuperen números de teléfono para un nombre y una fecha de nacimiento dados:

CREATE UNIQUE INDEX people_namedob_ix ON people
 (lastName,firstName,dateOfBirth) STORING (phoneNumber);

Índices invertidos

Un índice invertido puede utilizarse para indexar los elementos de una matriz o los atributos de un documento JSON. Ya vimos el funcionamiento interno de los índices invertidos en el Capítulo 2. Los índices invertidos también pueden utilizarse para datos espaciales.

Por ejemplo, supongamos que nuestra tabla people utiliza un documento JSON para almacenar los atributos de una persona:

CREATE TABLE people
 ( id UUID NOT NULL DEFAULT gen_random_uuid(),
   personData JSONB );

INSERT INTO people (personData)
VALUES('{
	  "firstName":"Guy",
        "lastName":"Harrison",
        "dob":"21-Jun-1960",
        "phone":"0419533988",
        "photo":"eyJhbGciOiJIUzI1NiIsI..."
     }');

Podríamos crear un índice invertido de la siguiente manera:

CREATE INVERTED INDEX people_inv_idx ON
people(personData);

Que permitiría realizar consultas en el documento JSON como ésta:

SELECT *
FROM people
WHERE personData @> '{"phone":"0419533988"}';

Ten en cuenta que los índices invertidos indexan todos los atributos del documento JSON, no sólo aquellos en los que quieres buscar. Esto puede dar lugar a un índice muy grande. Por tanto, puede que te resulte más útil crear una columna calculada sobre el atributo JSON y luego indexar sobre esa columna calculada:

ALTER TABLE people ADD phone STRING AS (personData->>'phone') VIRTUAL;

CREATE INDEX people_phone_idx ON people(phone);

Índices separados por hash

Si trabajas con una tabla que debe indexarse con claves secuenciales, debes utilizar índices con hash. Los índices separados por hash distribuyen el tráfico secuencial uniformemente por los rangos, eliminando los puntos calientes de un solo rango y mejorando el rendimiento de escritura en los índices con claves secuenciales, con un pequeño coste para el rendimiento de lectura:

CREATE TABLE people
( id INT PRIMARY KEY,
  firstName VARCHAR NOT NULL,
  lastName VARCHAR NOT NULL,
  dateOfBirth timestamp NOT NULL,
  phoneNumber VARCHAR NOT NULL,
  serialNo SERIAL ,
  INDEX serialNo_idx (serialNo) USING HASH WITH BUCKET_COUNT=4);

En la siguiente sección hablaremos con más detalle de los índices separados por hash, así como de otros temas de indexación más avanzados.

CREAR TABLA COMO SELECT

La cláusula AS SELECT de CREATE TABLE nos permite crear una nueva tabla que tenga los datos y atributos de una sentencia SQL SELECT. Se pueden especificar columnas, restricciones e índices para una tabla existente, pero deben coincidir con los tipos de datos y el número de columnas que devuelve la sentencia SELECT. Por ejemplo, aquí creamos una tabla basada en una JOIN y agregada de dos tablas de la base de datos movr:

CREATE TABLE user_ride_counts AS
SELECT u.name, COUNT(u.name) AS rides
  FROM "users" AS u JOIN "rides" AS r
    ON (u.id=r.rider_id)
 GROUP BY u.name;

Ten en cuenta que mientras que CREATE TABLE AS SELECT puede utilizarse para crear tablas resumen y similares, CREATE MATERIALIZED VIEW ofrece una alternativa más funcional.

Alterar tablas

La sentencia ALTER TABLE permite añadir, modificar, renombrar o eliminar columnas o restricciones de la tabla , así como validar y particionar restricciones. La Figura 4-9 muestra la sintaxis.

Alterar las estructuras de las tablas en línea no es algo que deba emprenderse a la ligera, aunque CockroachDB proporciona mecanismos muy avanzados para propagar tales cambios sin afectar a la disponibilidad y con un impacto mínimo en el rendimiento. Discutiremos los procedimientos para los cambios de esquema en línea en capítulos posteriores.

Figura 4-9. ALTER TABLE declaración

Soltar tablas

Las tablas se pueden eliminar utilizando la sentencia DROP TABLE . La Figura 4-10 muestra la sintaxis.

Figura 4-10. DROP TABLE declaración

Se puede eliminar más de una tabla con una sola sentencia DROP TABLE . La palabra clave CASCADE hace que también se eliminen los objetos dependientes, como las vistas o las restricciones de clave externa. RESTRICT-por defecto- tiene el efecto contrario; si hay algún objeto dependiente, la tabla no se eliminará.

DROP CASCADE y claves foráneas

DROP TABLE…CASCADE eliminará cualquier restricción de clave externa que haga referencia a la tabla, pero no eliminará las tablas o filas que contengan esas claves externas. El resultado final serán referencias "colgantes" en esas tablas.

Debido a este carácter incompleto, y a que puede ser difícil estar seguro de lo que hará exactamente CASCADE, suele ser mejor eliminar manualmente todas las dependencias de una tabla antes de eliminarla.

Vistas

Una vista estándar es una definición de consulta almacenada en la base de datos que define una tabla virtual. Se puede hacer referencia a esta tabla virtual del mismo modo que a una tabla normal. Las Expresiones Comunes de Tabla pueden considerarse como una forma de crear una vista temporal para una única sentencia SQL. Si tuvieras una Expresión Común de Tabla que quisieras compartir entre sentencias SQL, entonces una vista sería una solución lógica.

Una vista materializada almacena los resultados de la definición de la vista en la base de datos, de modo que no sea necesario volver a ejecutarla cada vez que se encuentre. Esto mejora el rendimiento, pero puede dar lugar a resultados obsoletos. Si consideras una vista como una consulta almacenada, una vista materializada puede considerarse como un resultado almacenado.

La Figura 4-11 muestra la sintaxis de la declaración CREATE VIEW.

Figura 4-11. DeclaraciónCREATE VIEW

La sentencia REFRESH MATERIALIZED VIEW puede utilizarse para actualizar los datos subyacentes a una vista materializada.

Insertar datos

Podemos cargar datos en una nueva tabla utilizando la sentencia CREATE TABLE AS SELECT de la que hemos hablado antes, utilizando la sentencia INSERT dentro de un programa o desde el shell de la línea de comandos, o cargando datos externos mediante la sentencia IMPORT. También existen utilidades no SQL que insertan datos; las veremos en el Capítulo 7.

La venerable sentencia INSERT añade datos a una tabla existente. La Figura 4-12 ilustra una sintaxis simplificada de la sentencia INSERT.

Figura 4-12. DeclaraciónINSERT

INSERT toma un conjunto de valores o una sentencia SELECT. Por ejemplo, en el siguiente ejemplo, insertamos una única fila en la tabla people:

INSERT INTO people (firstName, lastName, dateOfBirth)
VALUES('Guy', 'Harrison', '21-JUN-1960');

La cláusula VALUES de la sentencia INSERT puede aceptar valores de matriz, insertando más de una fila en una sola ejecución:

INSERT INTO people (firstName, lastName, dateOfBirth)
VALUES ('Guy', 'Harrison', '21-JUN-1960'),
       ('Michael', 'Harrison', '19-APR-1994'),
        ('Oriana', 'Harrison', '18-JUN-2020');

Existen formas alternativas de insertar lotes en los distintos controladores del lenguaje de programación, y mostraremos algunos ejemplos en el Capítulo 7.

Se puede especificar una sentencia SELECT como origen de los datos insertados:

INSERT INTO people (firstName, lastName, dateOfBirth)
SELECT firstName, lastName, dateOfBirth
  FROM peopleStagingData;

La cláusula RETURNING permite devolver al usuario los datos insertados. Los datos devueltos incluirán no sólo las variables que se insertaron, sino también cualquier dato autogenerado. Por ejemplo, en este caso, INSERT datos sin especificar un valor de ID y nos devuelve los valores de ID que se crearon:

INSERT INTO people (firstName, lastName, dateOfBirth)
VALUES ('Guy', 'Harrison', '21-JUN-1960'),
       ('Michael', 'Harrison', '19-APR-1994'),
        ('Oriana', 'Harrison', '18-JUN-2020')
  RETURNING id;

La cláusula ON CONFLICT te permite controlar lo que ocurre si un INSERT infringe una restricción de unicidad. La Figura 4-13 muestra la sintaxis.

Figura 4-13. CláusulaON CONFLICT

Sin una cláusula ON CONFLICT , una violación de la restricción de unicidad hará que se aborte toda la sentencia INSERT. DO NOTHING permite que la sentencia INSERT en su conjunto tenga éxito, pero ignora cualquier inserción que viole la cláusula de unicidad. La cláusula DO UPDATE te permite especificar una sentencia UPDATE que se ejecuta en lugar de la INSERT. La funcionalidad DO UPDATE es similar en funcionalidad a la sentencia UPSERT que se comenta más adelante en este capítulo.

ACTUALIZACIÓN

La sentencia UPDATE modifica los datos existentes en una tabla.La Figura 4-14 muestra una sintaxis simplificada de la sentencia UPDATE.

Figura 4-14. DeclaraciónUPDATE

Una sentencia UPDATE puede especificar valores estáticos como en el siguiente ejemplo:

UPDATE users
  SET address = '201 E Randolph St',
  	  city='amsterdam'
 WHERE name='Maria Weber';

Alternativamente, los valores pueden ser una expresión que haga referencia a valores existentes:

UPDATE user_promo_codes
   SET usage_count=usage_count+1
  WHERE user_id='297fcb80-b67a-4c8b-bf9f-72c404f97fe8';

O la UPDATE puede utilizar una subconsulta para obtener los valores:

 UPDATE rides SET (revenue, start_address) =
    (SELECT revenue, end_address FROM rides
      WHERE id = '94fdf3b6-45a1-4800-8000-000000000123')
 WHERE id = '851eb851-eb85-4000-8000-000000000104';

La cláusula RETURNING puede utilizarse para ver las columnas modificadas. Esto es especialmente útil si una columna está siendo actualizada por una función, y queremos devolver el valor modificado a la aplicación :

UPDATE user_promo_codes
   SET usage_count=usage_count+1
  WHERE user_id='297fcb80-b67a-4c8b-bf9f-72c404f97fe8'
 RETURNING (usage_count);

UPSERT

UPSERT puede insertar nuevos datos y actualizar los datos existentes en una tabla en una sola operación. Si los datos de entrada no violan ninguna restricción de unicidad, se insertan. Si un dato de entrada coincide con una clave primaria existente, se actualizan los valores de esa fila.

En CockroachDB, la cláusula ON CONFLICT de INSERT proporciona un mecanismo similar, aunque más flexible. Cuando no se necesita esta flexibilidad, es probable que UPSERT sea más rápida que una sentencia similar INSERT...ON CONFLICT DO UPDATE .

La Figura 4-15 muestra la sintaxis de la declaración UPSERT.

Figura 4-15. DeclaraciónUPSERT

El UPSERT compara la clave-valor primaria de cada fila proporcionada. Si la clave primaria no se encuentra en la tabla existente, se crea una nueva fila. En caso contrario, se actualiza la fila existente con los nuevos valores proporcionados.

La cláusula RETURNING puede utilizarse para devolver una lista de filas actualizadas o insertadas.

En este ejemplo, la clave primaria de user_promo_codes es (city, user_id, code). Si un usuario ya tiene una entrada para esa combinación en la tabla, entonces esa fila se actualiza con un user_count de 0. De lo contrario, se crea una nueva fila con esos valores.

UPSERT INTO user_promo_codes
  (user_id,city,code,timestamp,usage_count)
SELECT id,city,'NewPromo',now(),0
  FROM "users";

BORRAR

DELETE permite eliminar datos de una tabla . La Figura 4-16 muestra una sintaxis simplificada de la sentencia DELETE.

Figura 4-16. DeclaraciónDELETE

La mayoría de las veces, una sentencia DELETE acepta una cláusula WHERE y no mucho más. Por ejemplo, aquí eliminamos una única fila de la tabla people:

DELETE FROM people
 WHERE firstName='Guy'
   AND lastName='Harrison';

La cláusula RETURNING puede devolver detalles de las filas eliminadas. Por ejemplo

DELETE FROM user_promo_codes
 WHERE code='NewPromo'
RETURNING(user_id);

También puedes incluir una cláusula ORDER BY y LIMIT para realizar borrados por lotes de forma incremental. Por ejemplo, puedes construir una sentencia DELETE para eliminar las 1.000 filas más antiguas. Consulta la documentación de CockroachDB para obtener más información.

TRUNCAR

TRUNCATE proporciona un mecanismo rápido para eliminar todas las filas de una tabla. Internamente, se implementa como un DROP TABLE seguido de un CREATE TABLE . TRUNCATE no es transaccional-no puedes ROLLBACK un TRUNCATE.

IMPORTAR/IMPORTAR EN

La sentencia IMPORT importa los siguientes tipos de datos a CockroachDB:

• Avro

• Valores separados por comas (CSV)/Valores separados por tabulaciones (TSV)

• Archivos de volcado Postgres

• Archivos de volcado de MySQL

• Archivos de volcado de CockroachDB

IMPORT crea una tabla nueva, mientras que IMPORT INTO permite importar a una tabla existente.

Los archivos a importar deben existir en un cubo de almacenamiento en la nube -Google Cloud Storage, Amazon S3 o Azure Blob storage- desde una dirección HTTP o desde el sistema de archivos local ("nodelocal").

Hablaremos de las distintas opciones para cargar datos en CockroachDB en el Capítulo 7. Sin embargo, por ahora, vamos a crear una nueva tabla customers a partir de un archivo CSV:

 IMPORT INTO TABLE customers (
        id INT PRIMARY KEY,
        name STRING,
        INDEX name_idx (name)
);
CSV DATA ('nodelocal://1/customers.csv');
        job_id       |  status   | fra | rows | index_entries | bytes
---------------------+-----------+-----+------+---------------+--------
  659162639684534273 | succeeded |   1 |    1 |             1 |    47
(1 row)

Time: 934ms total (execution 933ms / network 1ms)

Para un clúster de demostración de un solo nodo, la ubicación de nodelocal dependerá en cierta medida de tu instalación, pero a menudo estará en un directorio extern situado bajo el directorio de instalación de CockroachDB.

Declaraciones transaccionales

Hablamos mucho sobre las transacciones de CockroachDB en el Capítulo 2, así que repasa ese capítulo si necesitas un repaso sobre cómo funcionan las transacciones de CockroachDB. Desde el punto de vista del lenguaje SQL, CockroachDB admite las sentencias de control transaccional SQL estándar.

COMENZAR Transacción

La sentencia BEGIN inicia una transacción y establece sus propiedades. La Figura 4-17 muestra la sintaxis.

PRIORITY establece la prioridad de la transacción. En caso de conflicto, es menos probable que se reintenten las transacciones con prioridad HIGH.

READ ONLY especifica que la transacción es de sólo lectura y no modificará los datos.

Figura 4-17. TransacciónBEGIN

AS OF SYSTEM TIME permite a una transacción de READ ONLY ver los datos de una instantánea del historial de la base de datos. Volveremos sobre esto en unas páginas.

GUARDAR PUNTO

SAVEPOINT crea un punto de reversión con nombre que puede utilizarse como objetivo de una sentencia ROLLBACK. Esto permite descartar una parte de una transacción sin descartar todo el trabajo de la transacción. Consulta la sección ROLLBACK para más detalles.

COMPROMETE

La sentencia COMMIT compromete las transacciones actuales, haciendo permanentes los cambios.

Ten en cuenta que algunas transacciones pueden requerir la intervención del cliente para manejar escenarios de reintento. Estos patrones se explorarán en el Capítulo 6.

ROLLBACK

ROLLBACK aborta la transacción actual . Opcionalmente, podemos ROLLBACK a un punto de guardado, que deshace sólo las sentencias emitidas después de la SAVEPOINT.

Por ejemplo, en el siguiente ejemplo, la inserción del árbol numérico mal escrito se retrocede y se corrige sin abandonar la transacción en su conjunto:

BEGIN ;

INSERT INTO numbers VALUES(1,'one');
INSERT INTO numbers VALUES(2,'two');
SAVEPOINT two;

INSERT INTO numbers VALUES(3,'tree');
ROLLBACK TO SAVEPOINT two;

INSERT INTO numbers VALUES(3,'three');
COMMIT;

SELECCIONAR PARA ACTUALIZAR

La cláusula FOR UPDATE de una sentencia SELECT bloquea las filas devueltas por una consulta, asegurando que no puedan ser modificadas por otra transacción entre el momento en que se leen y cuando finaliza la transacción. Suele utilizarse para implementar el patrón de bloqueo pesimista que veremos en el Capítulo 6.

Una consulta FOR UPDATE debe ejecutarse dentro de una transacción. De lo contrario, los bloqueos se liberan al finalizar la sentencia SELECT.

Una FOR UPDATE emitida dentro de una transacción bloqueará, por defecto, otras sentencias FOR UPDATE sobre las mismas filas u otras transacciones que intenten actualizar esas filas para que no se completen hasta que se emita una COMMIT o ROLLBACK. Sin embargo, si una transacción de mayor prioridad intenta actualizar las filas o intenta emitir una FOR UPDATE , la transacción de menor prioridad será abortada y tendrá que volver a intentarlo.

Hablaremos de la mecánica de los reintentos de transacciones en el Capítulo 6.

La Figura 4-18 ilustra dos sentencias FOR UPDATE ejecutándose simultáneamente. La primera FOR UPDATE mantiene bloqueos en las filas afectadas, impidiendo que la segunda sesión obtenga esos bloqueos hasta que la primera sesión complete su transacción.

Figura 4-18. Comportamiento de la cláusulaFOR UPDATE

A LA HORA DEL SISTEMA

La cláusula AS OF SYSTEM TIME puede aplicarse a las sentencias SELECT y BEGIN TRANSACTION , así como en las operaciones BACKUP y RESTORE. AS OF SYSTEM TIME especifica que una sentencia SELECT o todas las sentencias de una operación READ ONLY deben ejecutarse en una instantánea de la base de datos en ese momento del sistema. Estas instantáneas están disponibles gracias a la arquitectura MVCC descrita en el Capítulo 2.

La hora puede especificarse como un desfase o como una marca de tiempo absoluta, como en los dos ejemplos siguientes:

SELECT * FROM rides r
   AS OF SYSTEM TIME '-1d';

 SELECT * FROM rides r
   AS OF SYSTEM TIME '2021-5-22 18:02:52.0+00:00';

El tiempo especificado no puede ser mayor en segundos que el parámetro de configuración de la zona de replicación ttlseconds, que controla la antigüedad máxima de las instantáneas MVCC.

También es posible especificar lecturas estancadas delimitadas de utilizando el argumento with_max_staleness:

SELECT * FROM rides r
    AS OF SYSTEM TIME with_max_staleness('10s')
 WHERE city='amsterdam'
   AND id='aaaae297-396d-4800-8000-0000000208d6';

Las lecturas antiguas limitadas pueden utilizarse para optimizar el rendimiento en implementaciones distribuidas, permitiendo a CockroachDB satisfacer la lectura de réplicas locales que pueden contener datos ligeramente antiguos. Volveremos a las lecturas antiguas limitadas en el Capítulo 11.

Otros objetivos del lenguaje de definición de datos

Hasta ahora, hemos visto SQL para crear, alterar y manipular datos en tablas e índices. Estos objetos representan el núcleo de la funcionalidad de la base de datos en CockroachDB, como en otras bases de datos SQL. Sin embargo, el Lenguaje de Definición de Datos (DDL) de CockroachDB proporciona soporte para una gran variedad de otros objetos utilizados con menos frecuencia. Una referencia completa de todos estos objetos ocuparía más espacio del que disponemos aquí: consulta la documentación de CockroachDB para obtener una lista completa de CockroachDB SQL.

En la Tabla 4-4 se enumeran algunos de los otros objetos que pueden manipularse en las sentencias CREATE, ALTER y DROP.

Tabla 4-4. Otros objetos del esquema CockroachDB

Object	Descripción
Database	Una base de datos es un espacio de nombres dentro de un clúster CockroachDB que contiene esquemas, tablas, índices y otros objetos. Las bases de datos se suelen utilizar para separar objetos que tienen responsabilidades de aplicación o políticas de seguridad distintas.
Schema	Un esquema es una colección de tablas e índices que pertenecen al mismo modelo relacional. En la mayoría de las bases de datos, las tablas se crean en el esquema PUBLIC por defecto.
Sequence	A menudo se utilizan secuencias para crear valores de clave primaria; sin embargo, en CockroachDB suele haber alternativas mejores. Consulta el Capítulo 5 para obtener más orientación sobre la generación de claves primarias.
Role	Un rol se utiliza para agrupar privilegios de base de datos y de esquema, que luego pueden concederse a los usuarios como una unidad. Consulta el Capítulo 12 para obtener más detalles sobre las prácticas de seguridad de CockroachDB.
Type	En CockroachDB, un tipo es un conjunto enumerado de valores que pueden aplicarse a una columna en una sentencia CREATE o ALTER TABLE .
User	Un usuario es una cuenta que puede utilizarse para acceder a la base de datos y a la que se pueden asignar privilegios específicos. Consulta el Capítulo 12 para obtener más detalles sobre las prácticas de seguridad de CockroachDB.
Statistics	Las estadísticas consisten en información sobre los datos de una tabla determinada que el optimizador SQL utiliza para elaborar el mejor plan de ejecución posible para una sentencia SQL. Consulta el Capítulo 8 para obtener más información sobre el ajuste de consultas.
changefeed	Un canal de cambios transmite los cambios a nivel de fila de las tablas nominadas a un programa cliente. Consulta el Capítulo 7 para obtener más información sobre la implementación de los flujos de cambios.
Schedule	Una programación controla la ejecución periódica de las copias de seguridad. Consulta el Capítulo 11 para orientarte sobre las políticas de copias de seguridad.

Comandos administrativos

CockroachDB admite comandos para mantener en la autenticación de los usuarios y su autoridad para realizar operaciones en la base de datos. También tiene un programador de tareas que puede utilizarse para programar operaciones de copia de seguridad y restauración, así como cambios programados del esquema. Otros comandos permiten mantener la topología del clúster.

Por lo general, estos comandos están estrechamente vinculados a operaciones administrativas concretas, que trataremos en capítulos posteriores, por lo que nos abstendremos de definirlos en detalle aquí. Siempre puedes consultar sus definiciones en la documentación de CockroachDB. La Tabla 4-5 resume los más significativos de estos comandos.

Tabla 4-5. Comandos administrativos de CockroachDB

Mando	Descripción
CANCEL JOB	Cancela trabajos de larga duración, como copias de seguridad, cambios de esquema o recopilación de estadísticas.
CANCEL QUERY	Cancelar una consulta en ejecución.
CANCEL SESSION	Cancelar y desconectar una sesión actualmente conectada.
CONFIGURE ZONE	CONFIGURE ZONE puede utilizarse para modificar las zonas de replicación de tablas, bases de datos, rangos o particiones. Consulta el Capítulo 10 para obtener más información sobre la configuración de zonas.
SET CLUSTER SETTING	Cambia un parámetro de configuración del clúster.
EXPLAIN	Mostrar un plan de ejecución de una sentencia SQL. Veremos EXPLAIN en detalle en el Capítulo 8.
EXPORT	Vuelca la salida SQL a archivos CSV.
SHOW/CANCEL/PAUSE JOBS	Gestiona trabajos en segundo plano -importaciones, copias de seguridad, cambios de esquema, etc.- en la base de datos.
SET LOCALITY	Cambia la localidad de una tabla en una base de datos multirregión. Para más información, consulta el Capítulo 10.
SET TRACING	Activa el seguimiento de una sesión. Hablaremos de ello en el Capítulo 8.
SHOW RANGES	Muestra cómo se segmenta una tabla, índice o base de datos en rangos. Consulta el Capítulo 2 para saber cómo divide CockroachDB los datos en rangos.
SPLIT AT	Fuerza una división de rango en la fila especificada de una tabla o índice.
BACKUP	Crea una copia de seguridad coherente de una tabla o base de datos. Consulta el Capítulo 11 para obtener orientación sobre las copias de seguridad y la alta disponibilidad.
SHOW STATISTICS	Mostrar estadísticas del optimizador para una tabla.
SHOW TRACE FOR SESSION	Muestra la información de seguimiento de una sesión creada por el comando SET TRACING .
SHOW TRANSACTIONS	Mostrar las transacciones en curso
SHOW SESSION	Mostrar sesiones en el nodo local o en todo el clúster.

El esquema de información

El esquema de información es un esquema especial en cada base de datos que contiene metadatos sobre los demás objetos de la base de datos; en CockroachDB se llama INFORMATION_SCHEMA. Puedes utilizar el esquema de información para descubrir los nombres y tipos de objetos de la base de datos. Por ejemplo, puedes utilizar el esquema de información para listar todos los objetos del esquema information_schema:

SELECT * FROM information_schema."tables"
 WHERE table_schema='information_schema';

O puedes utilizar information_schema para mostrar las columnas de una tabla:

SELECT column_name,data_type, is_nullable,column_default
 FROM information_schema.COLUMNS WHERE TABLE_NAME='customers';

El esquema de información es especialmente útil cuando se escriben aplicaciones con un modelo de datos desconocido. Por ejemplo, herramientas GUI como DBeaver utilizan el esquema de información para rellenar el árbol de la base de datos y mostrar información sobre tablas e índices.

El esquema de información está definido por las normas ANSI e implementado por muchas bases de datos relacionales. CockroachDB también incluye algunas tablas internas específicas del sistema CockroachDB en el esquema crdb_internal. Puedes encontrar información sobre estas tablas en la documentación de CockroachDB.

Resumen

En este capítulo, hemos repasado los fundamentos del lenguaje SQL para crear, consultar y modificar datos dentro de la base de datos CockroachDB.

Una definición completa de todos los elementos sintácticos del SQL de CockroachDB llevaría un libro entero, por lo que nos hemos centrado principalmente en las características principales del lenguaje SQL, con cierto énfasis en las características específicas de CockroachDB. Para conocer la sintaxis detallada y los detalles de los comandos administrativos de CockroachDB, consulta ladocumentación en línea de CockroachDB.

SQL es el lenguaje de CockroachDB, así que, por supuesto, seguiremos profundizando en el lenguaje SQL de CockroachDB a medida que nos adentremos en el mundo de CockroachDB.

¹ Ten en cuenta que user_ride_counts no está definido en el esquema por defecto de rides. Se define como SELECT u.name, COUNT(u.name) AS rides FROM "users" AS u JOIN "rides" AS r ON (u.id=r.rider_id) GROUP BY u.name .