Cargar datos JSON a Redshift usando el comando Copy

Clase 22 de 33 • Curso de AWS Redshift para Manejo de Big Data

En esta clase conoceremos cómo cargar datos en formato json a Redshift con el comando Copy, ya conoces las ventajas de este comando, ahora veremos cómo llevar nuestros datos a Redshift dado el caso que nuestro origen de datos se encuentre en un formato json.

Lo primero que debemos saber es que existen dos formas de cargar los datos en formato json en Redshift, una es la forma automática, en donde cada “key” del archivo json debe hacer match con cada columna que queramos llenar de una tabla en Redshift.

Es importante saber que las columnas en Redshift se crean en minúsculas, de manera que las “key” del archivo en json deben estar en minúscula también. La segunda forma es especificando la estructura del archivo para que este pueda ser cargado a la tabla, para ello utilizaremos un archivo extra denominado jsonpaths.

Carga automática:

Usando la estructura de tabla que hemos manejado en este curso:

--Create table
create  table  estudiante
( id  int2,
nombre  varchar(20),
apellido  varchar(20),
edad  int2,
fecha_ingreso  date );

Crearemos una estructura en Redshift que pueda satisfacer las necesidades de esta tabla, de modo que puede quedar algo así (recuerda que los “key” del archivo se debe llamar igual que en la tabla):

{
	"id": 4544,
	"nombre": "test_1",
	"apellido": "json_1",
	"edad": 33,
	"fecha_ingreso": "2020-08-01"
}
{
	"id": 23232,
	"nombre": "test_2",
	"apellido": "json_2",
	"edad": 22,
	"fecha_ingreso": "2020-08-03"
}

Ahora lo subimos a S3 con el nombre que queramos y procedemos a cargarlo de la siguiente manera:

copy  public.estudiante  from  's3://[tu_propia_ruta_del_archivo_json]'  credentials  'aws_iam_role=[tu_iam_role]'
format  as  json  'auto'  region  '[tu_region]';

Como se ve en la gráfica, la tabla se encuentra cargada con los datos que existen en el archivo json.

Carga con jsonpaths:

Esta carga consiste en determinar una estructura basada en el archivo json que pueda ser insertada en la tabla, como sabemos en un archivo json podemos tener datos no estructurados, pero Redshift requiere de data estructurada; de manera que lo primero será crear un pequeño ejemplo de un archivo json con datos no estructurada.

{
	"id": 4544,
	"nombre": "test_json3",
	"apellido": "test_json3",
	"edad": [
		24332,
		33,
		443,
		323232,
		43434
	],
	"fechas": [
		{
			"id": 0,
			"fecha_ingreso": "2015-05-01"
		},
		{
			"id": 1,
			"fecha_ingreso": "2016-05-30"
		}
	]
}

Vamos a suponer que de la lista de edades son la segunda posición o sea la posición [1] es la que yo requiero, y para las fechas, la fecha que en verdad me interesa es la contenida en el segundo bloque, para especificar estas reglas crearemos un nuevo archivo con la siguiente estructura y lo nombraremos “jsonpaths”:

{
	"jsonpaths": [
		"$['id']",
		"$['nombre']",
		"$['apellido']",
		"$['edad'][1]",
		"$['fechas'][1]['fecha_ingreso']"
	]
}

En donde el símbolo ($) indica la raíz del archivo json.

Ahora subimos nuestros dos archivos a S3 y ejecutamos la siguiente sentencia en nuestro editor SQL.

copy  public.estudiante  from  's3://[tu_propia_ruta_del_archivo_json]'  credentials  'aws_iam_role=[tu_iam_role]'
format  as  json  's3://[tu_propia_ruta_del_archivo_jsonpaths]'  region  'tu_propia_region';

Y como vemos, el registro fue cargado exitosamente.

Luis Lema

student•

Hay un error de sintaxis en el archivo de rutas jsonpaths luego de la raíz del archivo debe usarse corchetes en lugar de llaves quedando de esta forma:

{
	"jsonpaths": [
		"$['id']",
		"$['nombre']",
		"$['apellido']",
		"$['edad'][1]",
		"$['fechas'][1]['fecha_ingreso']"
	]
}

Jesús Guillén

student•

Efectivamente, gracias por el aporte!

JAVIER SANTIAGO SALGADO

student•

Gracias

Karla Verónica Álvarez Vázquez

student•

Muy buena explicación, todo muy simple y bien explicado. Gran comando el COPY.

JUAN ESTEBAN ROODRIGUEZ BELTRAN

student•

¿Como seria la configuración si se requiera ingresar todos los datos de fechas y edades?

Andrés Felipe Suarez

student•

Resultado ejemplo

JAVIER SANTIAGO SALGADO

student•

Carlos Javier Guevara Contreras

student•

interesante....

{
	"id": 4544,
	"nombre": "test_1",
	"apellido": "json_1",
	"edad": 33,
	"fecha_ingreso": "2020-08-01"
}
{
	"id": 23232,
	"nombre": "test_2",
	"apellido": "json_2",
	"edad": 22,
	"fecha_ingreso": "2020-08-03"
}

{
	"id": 4544,
	"nombre": "test_json3",
	"apellido": "test_json3",
	"edad": [
		24332,
		33,
		443,
		323232,
		43434
	],
	"fechas": [
		{
			"id": 0,
			"fecha_ingreso": "2015-05-01"
		},
		{
			"id": 1,
			"fecha_ingreso": "2016-05-30"
		}
	]
}

copy  public.estudiante  from  's3://[tu_propia_ruta_del_archivo_json]'  credentials  'aws_iam_role=[tu_iam_role]'
format  as  json  's3://[tu_propia_ruta_del_archivo_jsonpaths]'  region  'tu_propia_region';

Cargar datos JSON a Redshift usando el comando Copy

Primeros pasos en la arquitectura no transaccional

Manejo de Big Data con Reptiit en Amazon AWS

Data Warehouse y Modelo Dimensional en Amazon Repsheet

Bases de Datos Columnares: Eficiencia en Consultas Analíticas

Procesamiento de Datos con Repsheet y Clústeres SQL

Configura tu entorno de trabajo para Redshift

Configuración de IAM y S3 en AWS para Repsheet

Configuración de Clúster en Amazon Repsheet para Big Data

Conexión y Configuración de Repsheet con Clientes Externos

Carga de Datos a Redshift desde Amazon S3: Paso a Paso

Cómo diseñar tu base de datos para mejorar su desempeño

Compresión de Datos en Repsheet: Algoritmos y Aplicaciones

Algoritmos de Compresión de Datos: Musley y Otros Métodos Eficientes

Compresión de Datos en SQL: Evaluación y Comparación de Algoritmos

Compresión de Datos en Repsheet: Optimización y Análisis

Algoritmos de Distribución de Datos en Repsheet

Distribución de Datos en Tablas SQL con Repsheet

Llaves de Ordenamiento en Bases de Datos: Compuesta vs. Intercalada

Pruebas de Algoritmos de Ordenamiento en SQL con AWS S3 y Redshift

Consultas SQL y Algoritmos de Ordenamiento Avanzados

Optimización de Datos en Data Warehouses con Repsheet

Manejo de Tipos de Datos en Amazon Redshift

Optimización de Bases de Datos en Modelos Dimensionales

Manipular enormes cantidades de datos

Carga Masiva de Datos en Repshit con el Comando COPY

Cargar datos JSON a Redshift usando el comando Copy

Parámetros Comunes del Comando COPY en Amazon Redshift

Carga Masiva de Datos sin Delimitador en RedSheet

Inserción de Datos en Repsheet sin Archivos Planos

Actualización Eficiente de Datos en Repsheet con Tablas Auxiliares

Optimización de Bases de Datos con Analyze y Vacuum en Repsheet

Optimización de Bases de Datos: Estadísticas y Limpieza de Tablas

Buenas prácticas para diseñar y ejecutar consultas en tu base de datos

Buenas prácticas de SQL en bases de datos columnares

Optimización de Consultas SQL con Plan de Ejecución y Llaves de Ordenamiento

Análisis de comportamiento y descarga de datos con Redshift

Exportación de Datos desde Repsheet a Amazon S3 con Unload

Tablas útiles para administración en Repsheet

Conclusiones

Gestión de Datos y Consultas en Repsheat