Acelera las consultas analíticas con el motor de columnas en AlloyDB Omni.

1. Introducción

En este codelab, aprenderás a implementar AlloyDB Omni y a usar el motor de columnas para mejorar el rendimiento de las consultas.

7da257ba067ed1b1.png

Requisitos previos

  • Conocimientos básicos sobre la consola de Google Cloud
  • Habilidades básicas de la interfaz de línea de comandos y de Google Shell

Qué aprenderás

  • Cómo implementar AlloyDB Omni en una VM de GCE en Google Cloud
  • Cómo conectarse a AlloyDB Omni
  • Cómo cargar datos en AlloyDB Omni
  • Cómo habilitar el motor de columnas
  • Cómo verificar el motor de columnas en el modo automático
  • Cómo propagar el almacén de columnas de forma manual

Requisitos

  • Una cuenta de Google Cloud y un proyecto de Google Cloud
  • Un navegador web, como Chrome

2. Configuración y requisitos

Configuración del entorno de autoaprendizaje

  1. Accede a Google Cloud Console y crea un proyecto nuevo o reutiliza uno existente. Si aún no tienes una cuenta de Gmail o de Google Workspace, debes crear una.

fbef9caa1602edd0.png

a99b7ace416376c4.png

5e3ff691252acf41.png

  • El Nombre del proyecto es el nombre visible de los participantes de este proyecto. Es una cadena de caracteres que no se utiliza en las APIs de Google. Puedes actualizarla cuando quieras.
  • El ID del proyecto es único en todos los proyectos de Google Cloud y es inmutable (no se puede cambiar después de configurarlo). La consola de Cloud genera automáticamente una cadena única. Por lo general, no importa cuál sea. En la mayoría de los codelabs, deberás hacer referencia al ID de tu proyecto (suele identificarse como PROJECT_ID). Si no te gusta el ID que se generó, podrías generar otro aleatorio. También puedes probar uno propio y ver si está disponible. No se puede cambiar después de este paso y se usa el mismo durante todo el proyecto.
  • Recuerda que hay un tercer valor, un número de proyecto, que usan algunas APIs. Obtén más información sobre estos tres valores en la documentación.
  1. A continuación, deberás habilitar la facturación en la consola de Cloud para usar las APIs o los recursos de Cloud. Ejecutar este codelab no costará mucho, tal vez nada. Para cerrar recursos y evitar que se generen cobros más allá de este instructivo, puedes borrar los recursos que creaste o borrar el proyecto. Los usuarios nuevos de Google Cloud son aptos para participar en el programa Prueba gratuita de $300.

Inicia Cloud Shell

Si bien Google Cloud y Spanner se pueden operar de manera remota desde tu laptop, en este codelab usarás Google Cloud Shell, un entorno de línea de comandos que se ejecuta en la nube.

En Google Cloud Console, haz clic en el ícono de Cloud Shell en la barra de herramientas en la parte superior derecha:

55efc1aaa7a4d3ad.png

El aprovisionamiento y la conexión al entorno deberían tomar solo unos minutos. Cuando termine el proceso, debería ver algo como lo siguiente:

7ffe5cbb04455448.png

Esta máquina virtual está cargada con todas las herramientas de desarrollo que necesitarás. Ofrece un directorio principal persistente de 5 GB y se ejecuta en Google Cloud, lo que permite mejorar considerablemente el rendimiento de la red y la autenticación. Todo tu trabajo en este codelab se puede hacer en un navegador. No es necesario que instales nada.

3. Antes de comenzar

Habilita la API

Resultado:

En Cloud Shell, asegúrate de que tu ID del proyecto esté configurado:

PROJECT_ID=$(gcloud config get-value project)
echo $PROJECT_ID

Si no está definido en la configuración de Cloud Shell, configúralo con los siguientes comandos:

export PROJECT_ID=<your project>
gcloud config set project $PROJECT_ID

Habilita todos los servicios necesarios con el siguiente comando:

gcloud services enable compute.googleapis.com

Resultado esperado

student@cloudshell:~ (test-project-001-402417)$ PROJECT_ID=$(gcloud config get-value project)
Your active configuration is: [cloudshell-14650]
student@cloudshell:~ (test-project-001-402417)$ gcloud config set project test-project-001-402417
Updated property [core/project].
student@cloudshell:~ (test-project-001-402417)$ gcloud services enable compute.googleapis.com
Operation "operations/acat.p2-4470404856-1f44ebd8-894e-4356-bea7-b84165a57442" finished successfully.

4. Implementa AlloyDB Omni en GCE

Para implementar AlloyDB Omni en GCE, debemos preparar una máquina virtual con software y configuración compatibles. Este es un ejemplo de cómo implementar AlloyDB Omni en una VM basada en Debian.

Crea una VM de GCE

Necesitamos implementar una VM con una configuración aceptable para la CPU, la memoria y el almacenamiento. Usaremos la imagen predeterminada de Debian con el tamaño del disco del sistema aumentado a 20 GB para admitir los archivos de la base de datos de AlloyDB Omni.

Podemos usar Cloud Shell iniciado o una terminal con el SDK de Cloud instalado.

Todos los pasos también se describen en el inicio rápido de AlloyDB Omni.

Configura las variables de entorno para tu implementación.

export ZONE=us-central1-a
export MACHINE_TYPE=n2-highmem-2
export DISK_SIZE=20
export MACHINE_NAME=omni01

Luego, usamos gcloud para crear la VM de GCE.

gcloud compute instances create $MACHINE_NAME \
    --project=$(gcloud info --format='value(config.project)') \
    --zone=$ZONE  --machine-type=$MACHINE_TYPE \
    --metadata=enable-os-login=true \
    --create-disk=auto-delete=yes,boot=yes,size=$DISK_SIZE,image=projects/debian-cloud/global/images/$(gcloud compute images list --filter="family=debian-12 AND family!=debian-12-arm64" \
    --format="value(name)"),type=pd-ssd

Resultado esperado en la consola:

gleb@cloudshell:~ (gleb-test-short-001-415614)$ export ZONE=us-central1-a
export MACHINE_TYPE=n2-highmem-2
export DISK_SIZE=20
export MACHINE_NAME=omni01
gleb@cloudshell:~ (gleb-test-short-001-415614)$ gcloud compute instances create $MACHINE_NAME \
    --project=$(gcloud info --format='value(config.project)') \
    --zone=$ZONE  --machine-type=$MACHINE_TYPE \
    --metadata=enable-os-login=true \
    --create-disk=auto-delete=yes,boot=yes,size=$DISK_SIZE,image=projects/debian-cloud/global/images/$(gcloud compute images list --filter="family=debian-12 AND family!=debian-12-arm64" \
    --format="value(name)"),type=pd-ssd
Created [https://www.googleapis.com/compute/v1/projects/gleb-test-short-001-415614/zones/us-central1-a/instances/omni01].
WARNING: Some requests generated warnings:
 - Disk size: '20 GB' is larger than image size: '10 GB'. You might need to resize the root repartition manually if the operating system does not support automatic resizing. See https://cloud.google.com/compute/docs/disks/add-persistent-disk#resize_pd for details.

NAME: omni01
ZONE: us-central1-a
MACHINE_TYPE: n2-highmem-2
PREEMPTIBLE: 
INTERNAL_IP: 10.128.0.3
EXTERNAL_IP: 35.232.157.123
STATUS: RUNNING
gleb@cloudshell:~ (gleb-test-short-001-415614)$ 

Cómo instalar AlloyDB Omni

Conéctate a la VM creada:

gcloud compute ssh omni01 --zone $ZONE

Resultado esperado en la consola:

gleb@cloudshell:~ (gleb-test-short-001-415614)$ gcloud compute ssh omni01 --zone $ZONE
Warning: Permanently added 'compute.5615760774496706107' (ECDSA) to the list of known hosts.
Linux omni01.us-central1-a.c.gleb-test-short-003-421517.internal 6.1.0-20-cloud-amd64 #1 SMP PREEMPT_DYNAMIC Debian 6.1.85-1 (2024-04-11) x86_64

The programs included with the Debian GNU/Linux system are free software;
the exact distribution terms for each program are described in the
individual files in /usr/share/doc/*/copyright.

Debian GNU/Linux comes with ABSOLUTELY NO WARRANTY, to the extent
permitted by applicable law.
gleb@omni01:~$

Ejecuta el siguiente comando en la terminal conectada.

Instala Docker en la VM:

sudo apt update
sudo apt-get -y install docker.io

Resultado esperado de la consola (oculto):

gleb@omni01:~$ sudo apt update
sudo apt-get -y install docker.io
Get:1 file:/etc/apt/mirrors/debian.list Mirrorlist [30 B]
Get:5 file:/etc/apt/mirrors/debian-security.list Mirrorlist [39 B]
Get:7 https://packages.cloud.google.com/apt google-compute-engine-bookworm-stable InRelease [5146 B]
Get:8 https://packages.cloud.google.com/apt cloud-sdk-bookworm InRelease [6406 B]        
Get:9 https://packages.cloud.google.com/apt google-compute-engine-bookworm-stable/main amd64 Packages [1916 B]
Get:2 https://deb.debian.org/debian bookworm InRelease [151 kB]
...
Setting up binutils (2.40-2) ...
Setting up needrestart (3.6-4+deb12u1) ...
Processing triggers for man-db (2.11.2-2) ...
Processing triggers for libc-bin (2.36-9+deb12u4) ...
gleb@omni01:~$

Define la contraseña para el usuario de postgres:

export PGPASSWORD=<your password>

Crea un directorio para los datos de AlloyDB Omni. Es un enfoque opcional, pero recomendado. De forma predeterminada, los datos se crearían con la capa del sistema de archivos efímero de Docker y todo se destruiría cuando se borre el contenedor de Docker. Mantenerlo por separado te permite administrar contenedores independientemente de tus datos y, de manera opcional, colocarlos en un almacenamiento con mejores características de E/S.

Este es un comando que crea un directorio en el directorio principal del usuario, en el que se colocarán todos los datos:

mkdir -p $HOME/alloydb-data

Implementa el contenedor de AlloyDB Omni:

sudo docker run --name my-omni \
  -e POSTGRES_PASSWORD=$PGPASSWORD \
  -p 5432:5432 \
  -v $HOME/alloydb-data:/var/lib/postgresql/data \
  -v /dev/shm:/dev/shm \
  -d google/alloydbomni

Resultado esperado de la consola (oculto):

gleb@omni01:~$ export PGPASSWORD=StrongPassword
gleb@omni01:~$ sudo docker run --name my-omni \
  -e POSTGRES_PASSWORD=$PGPASSWORD \
  -p 5432:5432 \
  -v $HOME/alloydb-data:/var/lib/postgresql/data \
  -v /dev/shm:/dev/shm \
  -d google/alloydbomni
Unable to find image 'google/alloydbomni:latest' locally
latest: Pulling from google/alloydbomni
71215d55680c: Pull complete 
...
2e0ec3fe1804: Pull complete 
Digest: sha256:d6b155ea4c7363ef99bf45a9dc988ce5467df5ae8cd3c0f269ae9652dd1982a6
Status: Downloaded newer image for google/alloydbomni:latest
56de4ae0018314093c8b048f69a1e9efe67c6c8117f44c8e1dc829a2d4666cd2
gleb@omni01:~$ 

Instala el software del cliente de PostgreSQL en la VM (opcional, se espera que ya esté instalado):

sudo apt install -y  postgresql-client

Resultado esperado en la consola:

gleb@omni01:~$ sudo apt install -y  postgresql-client
Reading package lists... Done
Building dependency tree... Done
Reading state information... Done
postgresql-client is already the newest version (15+248).
0 upgraded, 0 newly installed, 0 to remove and 4 not upgraded.

Cómo conectarse a AlloyDB Omni:

psql -h localhost -U postgres

Resultado esperado en la consola:

gleb@omni01:~$ psql -h localhost -U postgres
psql (15.6 (Debian 15.6-0+deb12u1), server 15.5)
Type "help" for help.

postgres=# 

Desconecta el dispositivo AlloyDB Omni:

exit

Resultado esperado en la consola:

postgres=# exit
gleb@omni01:~$ 

5. Prepara una base de datos de prueba

Para probar el motor de columnas, debemos crear una base de datos y completarla con algunos datos de prueba.

Crea la base de datos

Conéctate a la VM de AlloyDB Omni y crea una base de datos

En la sesión de Cloud Shell, ejecuta lo siguiente:

gcloud config set project $(gcloud config get-value project)

Conéctate a la VM de AlloyDB Omni:

ZONE=us-central1-a
gcloud compute ssh omni01 --zone $ZONE

Resultado esperado en la consola:

student@cloudshell:~ (gleb-test-short-001-416213)$ gcloud config set project $(gcloud config get-value project)
Updated property [core/project].
student@cloudshell:~ (gleb-test-short-001-416213)$ ZONE=us-central1-a
gcloud compute ssh omni01 --zone $ZONE
Linux omni01.us-central1-a.c.gleb-test-short-003-421517.internal 6.1.0-20-cloud-amd64 #1 SMP PREEMPT_DYNAMIC Debian 6.1.85-1 (2024-04-11) x86_64

The programs included with the Debian GNU/Linux system are free software;
the exact distribution terms for each program are described in the
individual files in /usr/share/doc/*/copyright.

Debian GNU/Linux comes with ABSOLUTELY NO WARRANTY, to the extent
permitted by applicable law.
Last login: Mon Mar  4 18:17:55 2024 from 35.237.87.44
student@omni01:~$

En la sesión de SSH establecida, ejecuta lo siguiente:

export PGPASSWORD=<your password>
psql -h localhost -U postgres -c "CREATE DATABASE quickstart_db"

Resultado esperado en la consola:

student@omni01:~$ psql -h localhost -U postgres -c "CREATE DATABASE quickstart_db"
CREATE DATABASE
student@omni01:~$

Crea una tabla con datos de muestra

Para nuestras pruebas, usaremos datos públicos sobre los productores de seguros con licencia en Iowa. Puedes encontrar este conjunto de datos en el sitio web del Gobierno de Iowa: https://data.iowa.gov/Regulation/Insurance-Producers-Licensed-in-Iowa/n4cc-vqyk/about_data .

Primero, debemos crear una tabla.

En la VM de GCE, ejecuta lo siguiente:

psql -h localhost -U postgres -d quickstart_db -c "DROP TABLE if exists insurance_producers_licensed_in_iowa;
CREATE TABLE insurance_producers_licensed_in_iowa (
        npn int8,
        last_name text,
        first_name text,
        address_line_1 text,
        address_line_2 text,
        address_line_3 text,
        city text,
        state text,
        zip int4,
        firstactivedate timestamp,
        expirydate timestamp,
        business_phone text,
        email text,
        physical_location text,
        iowaresident text,
        loa_has_crop text,
        loa_has_surety text,
        loa_has_ah text,
        loa_has_life text,
        loa_has_variable text,
        loa_has_personal_lines text,
        loa_has_credit text,
        loa_has_excess text,
        loa_has_property text,
        loa_has_casualty text,
        loa_has_reciprocal text
);"

Resultado esperado en la consola:

otochkin@omni01:~$ psql -h localhost -U postgres -d quickstart_db -c "DROP TABLE if exists insurance_producers_licensed_in_iowa;
CREATE TABLE insurance_producers_licensed_in_iowa (
        npn int8,
        last_name text,
        first_name text,
        address_line_1 text,
        address_line_2 text,
        address_line_3 text,
        city text,
        state text,
        zip int4,
        firstactivedate timestamp,
        expirydate timestamp,
        business_phone text,
        email text,
        physical_location text,
        iowaresident text,
        loa_has_crop text,
        loa_has_surety text,
        loa_has_ah text,
        loa_has_life text,
        loa_has_variable text,
        loa_has_personal_lines text,
        loa_has_credit text,
        loa_has_excess text,
        loa_has_property text,
        loa_has_casualty text,
        loa_has_reciprocal text
);"
NOTICE:  table "insurance_producers_licensed_in_iowa" does not exist, skipping
DROP TABLE
CREATE TABLE
otochkin@omni01:~$

Carga datos en la tabla.

En la VM de GCE, ejecuta lo siguiente:

curl https://data.iowa.gov/api/views/n4cc-vqyk/rows.csv | psql -h localhost -U postgres -d quickstart_db -c "\copy insurance_producers_licensed_in_iowa from stdin csv header"

Resultado esperado en la consola:

otochkin@omni01:~$ curl https://data.iowa.gov/api/views/n4cc-vqyk/rows.csv | psql -h localhost -U postgres -d quickstart_db -c "\copy insurance_producers_licensed_in_iowa from stdin csv header"
  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100 39.3M    0 39.3M    0     0  1004k      0 --:--:--  0:00:40 --:--:-- 1028k
COPY 210898
otochkin@omni01:~$

Cargamos 210898 registros sobre productores de seguros en nuestra base de datos y podemos hacer algunas pruebas.

Cómo ejecutar consultas de prueba

Conéctate a quickstart_db con psql y habilita el tiempo para medir el tiempo de ejecución de nuestras consultas.

En la VM de GCE, ejecuta lo siguiente:

psql -h localhost -U postgres -d quickstart_db

Resultado esperado en la consola:

student@omni01:~$ psql -h localhost -U postgres -d quickstart_db
psql (13.14 (Debian 13.14-0+deb11u1), server 15.5
WARNING: psql major version 13, server major version 15.
         Some psql features might not work.
Type "help" for help.

quickstart_db=#

En la sesión de PSQL, ejecuta lo siguiente:

\timing

Resultado esperado en la consola:

quickstart_db=# \timing
Timing is on.
quickstart_db=# 

Encontremos las 5 ciudades principales por cantidad de productores de seguros que venden seguros de accidentes y salud cuya licencia es válida al menos durante los próximos 6 meses.

En la sesión de PSQL, ejecuta lo siguiente:

SELECT city, count(*) 
FROM insurance_producers_licensed_in_iowa 
WHERE loa_has_ah ='Yes' and expirydate > now() + interval '6 MONTH' 
GROUP BY city ORDER BY count(*) desc limit 5;

Resultado esperado en la consola:

quickstart_db=# SELECT city, count(*)
FROM insurance_producers_licensed_in_iowa
WHERE loa_has_ah ='Yes' and expirydate > now() + interval '6 MONTH'
GROUP BY city ORDER BY count(*) desc limit 5;
    city     | count
-------------+-------
 TAMPA       |  1885
 OMAHA       |  1656
 KANSAS CITY |  1279
 AUSTIN      |  1254
 MIAMI       |  1003
(5 rows)

Time: 94.965 ms

Ejecuta una consulta de prueba varias veces para obtener un tiempo de ejecución confiable.Podemos ver que el tiempo promedio para mostrar el resultado es de alrededor de 94 ms. En los siguientes pasos, habilitaremos el motor de columnas de AlloyDB y veremos si puede mejorar el rendimiento.

Sal de la sesión de psql:

exit

6. Habilitar el motor de columnas

Ahora debemos habilitar el motor de columnas en nuestro AlloyDB Omni.

Actualiza los parámetros de AlloyDB Omni

Debemos cambiar el parámetro de instancia "google_columnar_engine.enabled" a "on" para nuestro AlloyDB Omni, y esto requiere un reinicio.

Actualiza el archivo postgresql.conf en el directorio /var/alloydb/config y reinicia la instancia.

En la VM de GCE, ejecuta lo siguiente:

sudo docker exec my-omni /bin/bash -c "echo google_columnar_engine.enabled=true >>/var/lib/postgresql/data/postgresql.conf"
sudo docker exec my-omni /bin/bash -c "echo shared_preload_libraries=\'google_columnar_engine,google_job_scheduler,google_db_advisor,google_storage\' >>/var/lib/postgresql/data/postgresql.conf"
sudo docker stop my-omni
sudo docker start my-omni

Resultado esperado en la consola:

student@omni01:~$ sudo docker exec my-omni /bin/bash -c "echo google_columnar_engine.enabled=true >>/var/lib/postgresql/data/postgresql.conf"
sudo docker exec my-omni /bin/bash -c "echo shared_preload_libraries=\'google_columnar_engine,google_job_scheduler,google_db_advisor,google_storage\' >>/var/lib/postgresql/data/postgresql.conf"
sudo docker stop my-omni
sudo docker start my-omni
my-omni
my-omni
student@omni01:~$

Cómo verificar el motor de columnas

Conéctate a la base de datos con psql y verifica el motor de columnas.

Cómo conectarse a la base de datos de AlloyDB Omni

En la sesión SSH de la VM, conéctate a la base de datos:

psql -h localhost -U postgres -d quickstart_db -c "show google_columnar_engine.enabled"

El comando debería mostrar el motor de columnas habilitado.

Resultado esperado en la consola:

student@omni01:~$ psql -h localhost -U postgres -d quickstart_db -c "show google_columnar_engine.enabled"
 google_columnar_engine.enabled 
--------------------------------
 on
(1 row)

7. Comparación de rendimiento

Ahora podemos propagar la tienda del motor de columnas y verificar el rendimiento.

Población automática de almacenes de columnas

De forma predeterminada, la tarea que propaga la tienda se ejecuta cada hora. Para evitar esperas, reduciremos este tiempo a 10 minutos.

En la VM de GCE, ejecuta lo siguiente:

sudo docker exec my-omni /bin/bash -c "echo google_columnar_engine.auto_columnarization_schedule=\'EVERY 10 MINUTES\' >>/var/lib/postgresql/data/postgresql.conf"
sudo docker stop my-omni
sudo docker start my-omni

Este es el resultado esperado:

student@omni01:~$ sudo docker exec my-omni /bin/bash -c "echo google_columnar_engine.auto_columnarization_schedule=\'EVERY 5 MINUTES\' >>/var/lib/postgresql/data/postgresql.conf"
sudo docker stop my-omni
sudo docker start my-omni
my-omni
my-omni
student@omni01:~$

Verifica la configuración

En la VM de GCE, ejecuta lo siguiente:

psql -h localhost -U postgres -d quickstart_db -c "show google_columnar_engine.auto_columnarization_schedule;"

Resultado esperado:

student@omni01:~$ psql -h localhost -U postgres -d quickstart_db -c "show google_columnar_engine.auto_columnarization_schedule;"
 google_columnar_engine.auto_columnarization_schedule 
------------------------------------------------------
 EVERY 10 MINUTES
(1 row)

student@omni01:~$ 

Verifica los objetos en el almacén de columnas. Debe estar vacío.

En la VM de GCE, ejecuta lo siguiente:

psql -h localhost -U postgres -d quickstart_db -c "SELECT database_name, schema_name, relation_name, column_name FROM g_columnar_recommended_columns;"

Resultado esperado:

student@omni01:~$ psql -h localhost -U postgres -d quickstart_db -c "SELECT database_name, schema_name, relation_name, column_name FROM g_columnar_recommended_columns;"
 database_name | schema_name | relation_name | column_name 
---------------+-------------+---------------+-------------
(0 rows)

student@omni01:~$

Conéctate a la base de datos y ejecuta la misma consulta que ejecutamos antes varias veces.

En la VM de GCE, ejecuta lo siguiente:

psql -h localhost -U postgres -d quickstart_db 

En la sesión de PSQL.

Habilita la medición del tiempo

\timing

Ejecuta la consulta algunas veces:

SELECT city, count(*) 
FROM insurance_producers_licensed_in_iowa 
WHERE loa_has_ah ='Yes' and expirydate > now() + interval '6 MONTH' 
GROUP BY city ORDER BY count(*) desc limit 5;

Resultado esperado:

quickstart_db=# SELECT city, count(*)
FROM insurance_producers_licensed_in_iowa
WHERE loa_has_ah ='Yes' and expirydate > now() + interval '6 MONTH'
GROUP BY city ORDER BY count(*) desc limit 5;
    city     | count
-------------+-------
 TAMPA       |  1885
 OMAHA       |  1656
 KANSAS CITY |  1279
 AUSTIN      |  1254
 MIAMI       |  1003
(5 rows)

Time: 94.289 ms
quickstart_db=# SELECT city, count(*)
FROM insurance_producers_licensed_in_iowa
WHERE loa_has_ah ='Yes' and expirydate > now() + interval '6 MONTH'
GROUP BY city ORDER BY count(*) desc limit 5;
    city     | count
-------------+-------
 TAMPA       |  1885
 OMAHA       |  1656
 KANSAS CITY |  1279
 AUSTIN      |  1254
 MIAMI       |  1003
(5 rows)

Time: 94.608 ms
quickstart_db=#

Espera 10 minutos y comprueba si las columnas de la tabla insurance_producers_licensed_in_iowa se propagaron en el almacén de columnas.

SELECT database_name, schema_name, relation_name, column_name FROM g_columnar_recommended_columns;

Resultado esperado:

quickstart_db=# SELECT database_name, schema_name, relation_name, column_name FROM g_columnar_recommended_columns;
 database_name | schema_name |            relation_name             | column_name
---------------+-------------+--------------------------------------+-------------
 quickstart_db | public      | insurance_producers_licensed_in_iowa | city
 quickstart_db | public      | insurance_producers_licensed_in_iowa | expirydate
 quickstart_db | public      | insurance_producers_licensed_in_iowa | loa_has_ah
(3 rows)

Time: 0.643 ms

Ahora, podemos volver a ejecutar la consulta de la tabla insurance_producers_licensed_in_iowa y ver si mejora el rendimiento.

SELECT city, count(*)
FROM insurance_producers_licensed_in_iowa
WHERE loa_has_ah ='Yes' and expirydate > now() + interval '6 MONTH'
GROUP BY city ORDER BY count(*) desc limit 5;

Resultado esperado:

quickstart_db=# SELECT city, count(*)
FROM insurance_producers_licensed_in_iowa
WHERE loa_has_ah ='Yes' and expirydate > now() + interval '6 MONTH'
GROUP BY city ORDER BY count(*) desc limit 5;
    city     | count
-------------+-------
 TAMPA       |  1885
 OMAHA       |  1656
 KANSAS CITY |  1279
 AUSTIN      |  1254
 MIAMI       |  1003
(5 rows)

Time: 14.380 ms
quickstart_db=# SELECT city, count(*)
FROM insurance_producers_licensed_in_iowa
WHERE loa_has_ah ='Yes' and expirydate > now() + interval '6 MONTH'
GROUP BY city ORDER BY count(*) desc limit 5;
    city     | count
-------------+-------
 TAMPA       |  1885
 OMAHA       |  1656
 KANSAS CITY |  1279
 AUSTIN      |  1254
 MIAMI       |  1003
(5 rows)

Time: 13.279 ms

El tiempo de ejecución disminuyó de 94 ms a 14 ms. Si no ves ninguna mejora, puedes verificar si las columnas se propagaron correctamente al almacén de columnas. Para ello, consulta la vista g_columnar_columns.

SELECT relation_name,column_name,column_type,status,size_in_bytes from g_columnar_columns;

Resultado esperado:

quickstart_db=# select relation_name,column_name,column_type,status,size_in_bytes from g_columnar_columns;
            relation_name             | column_name | column_type | status | size_in_bytes
--------------------------------------+-------------+-------------+--------+---------------
 insurance_producers_licensed_in_iowa | city        | text        | Usable |        664231
 insurance_producers_licensed_in_iowa | expirydate  | timestamp   | Usable |        212434
 insurance_producers_licensed_in_iowa | loa_has_ah  | text        | Usable |        211734
(3 rows)

Ahora podemos verificar si el plan de ejecución de consultas usa el motor de columnas.

En la sesión de PSQL, ejecuta lo siguiente:

EXPLAIN (ANALYZE,SETTINGS,BUFFERS)
SELECT city, count(*)
FROM insurance_producers_licensed_in_iowa
WHERE loa_has_ah ='Yes' and expirydate > now() + interval '6 MONTH'
GROUP BY city ORDER BY count(*) desc limit 5;

Resultado esperado:

quickstart_db=# EXPLAIN (ANALYZE,SETTINGS,BUFFERS)
SELECT city, count(*)
FROM insurance_producers_licensed_in_iowa
WHERE loa_has_ah ='Yes' and expirydate > now() + interval '6 MONTH'
GROUP BY city ORDER BY count(*) desc limit 5;
                                                                                      QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=2279.72..2279.73 rows=5 width=17) (actual time=12.248..12.252 rows=5 loops=1)
   ->  Sort  (cost=2279.72..2292.91 rows=5277 width=17) (actual time=12.246..12.248 rows=5 loops=1)
         Sort Key: (count(*)) DESC
         Sort Method: top-N heapsort  Memory: 25kB
         ->  HashAggregate  (cost=2139.30..2192.07 rows=5277 width=17) (actual time=10.235..11.250 rows=7555 loops=1)
               Group Key: city
               Batches: 1  Memory Usage: 1169kB
               ->  Append  (cost=20.00..1669.24 rows=94012 width=9) (actual time=10.231..10.233 rows=94286 loops=1)
                     ->  Custom Scan (columnar scan) on insurance_producers_licensed_in_iowa  (cost=20.00..1665.22 rows=94011 width=9) (actual time=10.229..10.231 rows=94286 loops=1)
                           Filter: ((loa_has_ah = 'Yes'::text) AND (expirydate > (now() + '6 mons'::interval)))
                           Rows Removed by Columnar Filter: 116612
                           Rows Aggregated by Columnar Scan: 94286
                           Columnar cache search mode: native
                     ->  Seq Scan on insurance_producers_licensed_in_iowa  (cost=0.00..4.02 rows=1 width=9) (never executed)
                           Filter: ((loa_has_ah = 'Yes'::text) AND (expirydate > (now() + '6 mons'::interval)))
 Planning Time: 0.216 ms
 Execution Time: 12.353 ms

También podemos ver que la operación "Seq Scan" en el segmento de la tabla business_licenses nunca se ejecutó y, en su lugar, se usó "Custom Scan (columnar scan)". Eso nos ayudó a mejorar el tiempo de respuesta de 94 a 12 ms.

Si queremos borrar el contenido propagado automáticamente del motor de columnas, podemos hacerlo con la función de SQL google_columnar_engine_reset_recommendation.

En la sesión de PSQL, ejecuta lo siguiente:

SELECT google_columnar_engine_reset_recommendation(drop_columns => true);

Se borrarán las columnas propagadas y podrás verificarlo en las vistas g_columnar_columns y g_columnar_recommended_columns, como se mostró antes.

SELECT database_name, schema_name, relation_name, column_name FROM g_columnar_recommended_columns;
SELECT relation_name,column_name,column_type,status,size_in_bytes from g_columnar_columns;

Resultado esperado:

quickstart_db=# SELECT database_name, schema_name, relation_name, column_name FROM g_columnar_recommended_columns;
 database_name | schema_name | relation_name | column_name
---------------+-------------+---------------+-------------
(0 rows)

Time: 0.447 ms
quickstart_db=# select relation_name,column_name,column_type,status,size_in_bytes from g_columnar_columns;
 relation_name | column_name | column_type | status | size_in_bytes
---------------+-------------+-------------+--------+---------------
(0 rows)

Time: 0.556 ms
quickstart_db=#

Población manual del almacén de columnas

Podemos agregar columnas al almacén del motor de columnas de forma manual con funciones de SQL o especificar las entidades requeridas en las marcas de instancia para cargarlas automáticamente cuando se inicie la instancia.

Agreguemos las mismas columnas que antes con la función SQL google_columnar_engine_add.

En la sesión de PSQL, ejecuta lo siguiente:

SELECT google_columnar_engine_add(relation => 'insurance_producers_licensed_in_iowa', columns => 'city,expirydate,loa_has_ah');

Y podemos verificar el resultado con la misma vista g_columnar_columns:

SELECT relation_name,column_name,column_type,status,size_in_bytes from g_columnar_columns;

Resultado esperado:

quickstart_db=# SELECT relation_name,column_name,column_type,status,size_in_bytes from g_columnar_columns;
            relation_name             | column_name | column_type | status | size_in_bytes
--------------------------------------+-------------+-------------+--------+---------------
 insurance_producers_licensed_in_iowa | city        | text        | Usable |        664231
 insurance_producers_licensed_in_iowa | expirydate  | timestamp   | Usable |        212434
 insurance_producers_licensed_in_iowa | loa_has_ah  | text        | Usable |        211734
(3 rows)

Time: 0.692 ms
quickstart_db=#

Para verificar que se usa el almacén de columnas, ejecuta la misma consulta que antes y examina el plan de ejecución:

EXPLAIN (ANALYZE,SETTINGS,BUFFERS)
SELECT city, count(*)
FROM insurance_producers_licensed_in_iowa
WHERE loa_has_ah ='Yes' and expirydate > now() + interval '6 MONTH'
GROUP BY city ORDER BY count(*) desc limit 5;

Sal de la sesión de psql:

exit

Si reiniciamos el contenedor de AlloyDB Omni, podemos ver que se pierde toda la información de las columnas.

En la sesión de shell, ejecuta lo siguiente:

sudo docker stop my-omni
sudo docker start my-omni

Espera entre 5 y 10 segundos y ejecuta lo siguiente:

psql -h localhost -U postgres -d quickstart_db -c "SELECT relation_name,column_name,column_type,status,size_in_bytes from g_columnar_columns"

Resultado esperado:

student@omni01:~$ psql -h localhost -U postgres -d quickstart_db -c "SELECT relation_name,column_name,column_type,status,size_in_bytes from g_columnar_columns"
 relation_name | column_name | column_type | status | size_in_bytes
---------------+-------------+-------------+--------+---------------
(0 rows)

Para volver a propagar automáticamente las columnas durante el reinicio, podemos agregarlas como marcas de base de datos a nuestros parámetros de AlloyDB Omni. Agregaremos la marca google_columnar_engine.relations=‘quickstart_db.public.insurance_producers_licensed_in_iowa(city,expirydate,loa_has_ah)' y reiniciaremos el contenedor.

En la sesión de shell, ejecuta lo siguiente:

sudo docker exec my-omni /bin/bash -c "echo google_columnar_engine.relations=\'quickstart_db.public.insurance_producers_licensed_in_iowa\(city,expirydate,loa_has_ah\)\' >>/var/lib/postgresql/data/postgresql.conf"
sudo docker stop my-omni
sudo docker start my-omni

Después de eso, podemos ver que las columnas se agregaron al Columnar Store automáticamente después del inicio.

Espera entre 5 y 10 segundos y ejecuta lo siguiente:

psql -h localhost -U postgres -d quickstart_db -c "SELECT relation_name,column_name,column_type,status,size_in_bytes from g_columnar_columns"

Resultado esperado:

student@omni01:~$ psql -h localhost -U postgres -d quickstart_db -c "SELECT relation_name,column_name,column_type,status,size_in_bytes from g_columnar_columns"
            relation_name             | column_name | column_type | status | size_in_bytes
--------------------------------------+-------------+-------------+--------+---------------
 insurance_producers_licensed_in_iowa | city        | text        | Usable |        664231
 insurance_producers_licensed_in_iowa | expirydate  | timestamp   | Usable |        212434
 insurance_producers_licensed_in_iowa | loa_has_ah  | text        | Usable |        211734
(3 rows)

8. Limpia el entorno

Ahora podemos destruir nuestra VM de AlloyDB Omni.

Borra la VM de GCE

En Cloud Shell, ejecuta el siguiente comando:

export GCEVM=omni01
export ZONE=us-central1-a
gcloud compute instances delete $GCEVM \
    --zone=$ZONE \
    --quiet

Resultado esperado en la consola:

student@cloudshell:~ (test-project-001-402417)$ export GCEVM=omni01
export ZONE=us-central1-a
gcloud compute instances delete $GCEVM \
    --zone=$ZONE \
    --quiet
Deleted 

9. Felicitaciones

Felicitaciones por completar el codelab.

Temas abordados

  • Cómo implementar AlloyDB Omni en una VM de GCE en Google Cloud
  • Cómo conectarse a AlloyDB Omni
  • Cómo cargar datos en AlloyDB Omni
  • Cómo habilitar el motor de columnas
  • Cómo verificar el motor de columnas en el modo automático
  • Cómo propagar el almacén de columnas de forma manual

Puedes obtener más información para trabajar con el motor de columnas en la documentación.

10. Encuesta

Resultado:

¿Cómo usarás este instructivo?

Solo lo leeré Lo leeré y completaré los ejercicios