java - example - Copia CSV a Postgres con una matriz de tipo personalizado utilizando JDBC
jdbc postgresql 10 (3)
Tengo un tipo personalizado definido en mi base de datos como
CREATE TYPE address AS (ip inet, port int);
Y una tabla que usa este tipo en una matriz:
CREATE TABLE my_table (
addresses address[] NULL
)
Tengo un archivo CSV de muestra con los siguientes contenidos
{(10.10.10.1,80),(10.10.10.2,443)}
{(10.10.10.3,8080),(10.10.10.4,4040)}
Y utilizo el siguiente fragmento de código para realizar mi COPY:
Class.forName("org.postgresql.Driver");
String input = loadCsvFromFile();
Reader reader = new StringReader(input);
Connection connection = DriverManager.getConnection(
"jdbc:postgresql://db_host:5432/db_name", "user",
"password");
CopyManager copyManager = connection.unwrap(PGConnection.class).getCopyAPI();
String copyCommand = "COPY my_table (addresses) " +
"FROM STDIN WITH (" +
"DELIMITER ''/t'', " +
"FORMAT csv, " +
"NULL ''//N'', " +
"ESCAPE ''/"'', " +
"QUOTE ''/"'')";
copyManager.copyIn(copyCommand, reader);
La ejecución de este programa produce la siguiente excepción:
Exception in thread "main" org.postgresql.util.PSQLException: ERROR: malformed record literal: "(10.10.10.1"
Detail: Unexpected end of input.
Where: COPY only_address, line 1, column addresses: "{(10.10.10.1,80),(10.10.10.2,443)}"
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2422)
at org.postgresql.core.v3.QueryExecutorImpl.processCopyResults(QueryExecutorImpl.java:1114)
at org.postgresql.core.v3.QueryExecutorImpl.endCopy(QueryExecutorImpl.java:963)
at org.postgresql.core.v3.CopyInImpl.endCopy(CopyInImpl.java:43)
at org.postgresql.copy.CopyManager.copyIn(CopyManager.java:185)
at org.postgresql.copy.CopyManager.copyIn(CopyManager.java:160)
He intentado con diferentes combinaciones de paréntesis en la entrada pero parece que no puedo hacer funcionar la COPIA. ¿Alguna idea de dónde podría ir mal?
1NF
En primer lugar, creo que el diseño de su mesa es incorrecto porque no es compatible con 1NF . Cada campo solo debe contener atributos atómicos, pero ese no es el caso. ¿Por qué no una mesa como:
CREATE TABLE my_table (
id,
ip inet,
port int
)
Donde id es el número de su línea en el archivo de origen y ip / port una de las direcciones en esta línea? Data de muestra:
id | ip | port
-----------------------
1 | 10.10.10.1 | 80
1 | 10.10.10.2 | 443
2 | 10.10.10.3 | 8080
2 | 10.10.10.4 | 4040
...
Por lo tanto, podrá consultar su base de datos en una sola dirección (encuentre todas las direcciones asociadas, devuelva verdadero si dos direcciones están en la misma línea, cualquier otra cosa que desee ...).
Cargar los datos
Pero supongamos que sabes lo que estás haciendo. El principal problema aquí es que su archivo de datos de entrada está en un formato especial. Puede ser un archivo CSV de una sola columna, pero sería un archivo CSV muy degenerado. De todos modos, debe transformar las líneas antes de insertarlas en la base de datos. Tienes dos opciones:
- lees cada línea del archivo de entrada y haces un
INSERT(esto puede tomar un tiempo); - convierte el archivo de entrada en un archivo de texto con el formato esperado y usa
COPY.
Insertar uno por uno
Las primeras opciones parecen fáciles: para la primera fila del archivo csv, {(10.10.10.1,80),(10.10.10.2,443)} , debe ejecutar la consulta:
INSERT INTO my_table VALUES (ARRAY[(''10.10.10.1'',80),(''10.10.10.2'',443)]::address[], 4)
Para hacerlo, solo tienes que crear una nueva cadena:
String value = row.replaceAll("//{", "ARRAY[")
.replaceAll("//}", "]::address[]")
.replaceAll("//(([0-9.]+),", "''$1''");
String sql = String.format("INSERT INTO my_table VALUES (%s)", value);
Y ejecute la consulta para cada línea del archivo de entrada (o para una mejor seguridad, use una declaración preparada ).
Insertar con COPY
Daré más detalles sobre la segunda opción. Tienes que usar en el código de Java:
copyManager.copyIn(sql, from);
Donde la consulta de copia es una sentencia COPY FROM STDIN y es un lector. La declaración será:
COPY my_table (addresses) FROM STDIN WITH (FORMAT text);
Para alimentar al administrador de copias, necesita datos como (tenga en cuenta las citas):
{"(10.10.10.1,80)","(10.10.10.2,443)"}
{"(10.10.10.3,8080)","(10.10.10.4,4040)"}
Con un archivo temporal
La forma más sencilla de obtener los datos en el formato correcto es crear un archivo temporal. Lee cada línea del archivo de entrada y reemplaza ( por "( y ) por )" . Escriba esta línea procesada en un archivo temporal. Luego pase un lector sobre este archivo al administrador de copias.
Sobre la marcha
Con dos hilos puedes usar dos hilos:
el hilo 1 lee el archivo de entrada, procesa las líneas una por una y las escribe en un
PipedWriter.el hilo 2 pasa un
PipedReaderconectado alPipedWriteranterior al administrador de copias.
La principal dificultad es sincronizar los subprocesos de manera que el subproceso 2 comience a leer el PipedReader antes de que el subproceso 1 comience a escribir datos en el PipedWriter . Ver este proyecto mío para un ejemplo.
Con un lector personalizado El lector from podría ser una instancia de algo como (versión ingenua):
class DataReader extends Reader {
PushbackReader csvFileReader;
private boolean wasParenthese;
public DataReader(Reader csvFileReader) {
this.csvFileReader = new PushbackReader(csvFileReader, 1);
wasParenthese = false;
}
@Override
public void close() throws IOException {
this.csvFileReader.close();
}
@Override
public int read(char[] cbuf, int off, int len) throws IOException {
// rely on read()
for (int i = off; i < off + len; i++) {
int c = this.read();
if (c == -1) {
return i-off > 0 ? i-off : -1;
}
cbuf[i] = (char) c;
}
return len;
}
@Override
public int read() throws IOException {
final int c = this.csvFileReader.read();
if (c == ''('' && !this.wasParenthese) {
this.wasParenthese = true;
this.csvFileReader.unread(''('');
return ''"''; // add " before (
} else {
this.wasParenthese = false;
if (c == '')'') {
this.csvFileReader.unread(''"'');
return '')''; // add " after )
} else {
return c;
}
}
}
}
(Esta es una versión ingenua porque la forma correcta de hacerlo sería anular solo public int read(char[] cbuf, int off, int len) . Pero luego debe procesar el cbuf para agregar las comillas y almacenar los caracteres adicionales empujado a la derecha: esto es un poco tedioso). Ahora, si r es el lector para el archivo:
{(10.10.10.1,80),(10.10.10.2,443)}
{(10.10.10.3,8080),(10.10.10.4,4040)}
Solo usa:
Class.forName("org.postgresql.Driver");
Connection connection = DriverManager
.getConnection("jdbc:postgresql://db_host:5432/db_base", "user", "passwd");
CopyManager copyManager = connection.unwrap(PGConnection.class).getCopyAPI();
copyManager.copyIn("COPY my_table FROM STDIN WITH (FORMAT text)", new DataReader(r));
En carga a granel
Si está cargando una gran cantidad de datos, no olvide los consejos básicos : deshabilite la confirmación automática, elimine los índices y las restricciones, y use TRUNCATE y ANALYZE siguiente manera:
TRUNCATE my_table;
COPY ...;
ANALYZE my_table;
Esto acelerará la carga.
En formato CSV , cuando especifica un separador, no puede usarlo como un carácter en sus datos, ¡a menos que lo escape!
ejemplo de un archivo csv usando coma como separador
un registro correcto: data1, data2 resultados de análisis: [0] => data1 [1] => data2
uno incorrecto: data,1, data2 análisis de resultados: [0] => data [1] => 1 [2] => data2
finalmente, no necesita cargar su archivo como csv, sino como un archivo simple, así que reemplace su método loadCsvFromFile(); por
public String loadRecordsFromFile(File file) {
LineIterator it = FileUtils.lineIterator(file, "UTF-8");
StringBuilder sb = new StringBuilder();
try {
while (it.hasNext()) {
sb.append(it.nextLine()).append(System.nextLine);
}
}
finally {
LineIterator.closeQuietly(iterator);
}
return sb.toString();
}
No olvides añadir esta dependencia en tu archivo pom.
<!-- https://mvnrepository.com/artifact/commons-io/commons-io -->
<dependency>
<groupId>commons-io</groupId>
<artifactId>commons-io</artifactId>
<version>2.6</version>
</dependency>
O para descargar el JAR desde commons.apache.org
Vea https://git.mikael.io/mikaelhg/pg-object-csv-copy-poc/ para un proyecto con una prueba de JUnit que hace lo que usted quiere.
Básicamente, desea poder usar comas para dos cosas: para separar elementos de la matriz y para separar campos de tipo, pero NO desea que el análisis CSV interprete las comas como delineadores de campo.
Asi que
- quiere decirle al analizador CSV que considere que toda la fila es una cadena, un campo, lo que puede hacer encerrándolo entre comillas simples e informándole al analizador CSV acerca de esto, y
- desea que el analizador de campo PG considere que cada instancia de tipo de elemento de matriz se incluya entre comillas dobles.
Código:
copyManager.copyIn("COPY my_table (addresses) FROM STDIN WITH CSV QUOTE ''''''''", reader);
DML ejemplo 1:
COPY my_table (addresses) FROM STDIN WITH CSV QUOTE ''''''''
CSV ejemplo 1:
''{"(10.0.0.1,1)","(10.0.0.2,2)"}''
''{"(10.10.10.1,80)","(10.10.10.2,443)"}''
''{"(10.10.10.3,8080)","(10.10.10.4,4040)"}''
DML ejemplo 2, escapando las comillas dobles:
COPY my_table (addresses) FROM STDIN WITH CSV
CSV ejemplo 2, escapando las comillas dobles:
"{""(10.0.0.1,1)"",""(10.0.0.2,2)""}"
"{""(10.10.10.1,80)"",""(10.10.10.2,443)""}"
"{""(10.10.10.3,8080)"",""(10.10.10.4,4040)""}"
Clase completa de prueba JUnit:
package io.mikael.poc;
import com.google.common.io.CharStreams;
import org.junit.*;
import org.postgresql.PGConnection;
import org.postgresql.copy.CopyManager;
import org.testcontainers.containers.PostgreSQLContainer;
import java.io.*;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import static java.nio.charset.StandardCharsets.UTF_8;
public class CopyTest {
private Reader reader;
private Connection connection;
private CopyManager copyManager;
private static final String CREATE_TYPE = "CREATE TYPE address AS (ip inet, port int)";
private static final String CREATE_TABLE = "CREATE TABLE my_table (addresses address[] NULL)";
private String loadCsvFromFile(final String fileName) throws IOException {
try (InputStream is = getClass().getResourceAsStream(fileName)) {
return CharStreams.toString(new InputStreamReader(is, UTF_8));
}
}
@ClassRule
public static PostgreSQLContainer db = new PostgreSQLContainer("postgres:10-alpine");
@BeforeClass
public static void beforeClass() throws Exception {
Class.forName("org.postgresql.Driver");
}
@Before
public void before() throws Exception {
String input = loadCsvFromFile("/data_01.csv");
reader = new StringReader(input);
connection = DriverManager.getConnection(db.getJdbcUrl(), db.getUsername(), db.getPassword());
copyManager = connection.unwrap(PGConnection.class).getCopyAPI();
connection.setAutoCommit(false);
connection.beginRequest();
connection.prepareCall(CREATE_TYPE).execute();
connection.prepareCall(CREATE_TABLE).execute();
}
@After
public void after() throws Exception {
connection.rollback();
}
@Test
public void copyTest01() throws Exception {
copyManager.copyIn("COPY my_table (addresses) FROM STDIN WITH CSV QUOTE ''''''''", reader);
final StringWriter writer = new StringWriter();
copyManager.copyOut("COPY my_table TO STDOUT WITH CSV", writer);
System.out.printf("roundtrip:%n%s%n", writer.toString());
final ResultSet rs = connection.prepareStatement(
"SELECT array_to_json(array_agg(t)) FROM (SELECT addresses FROM my_table) t")
.executeQuery();
rs.next();
System.out.printf("json:%n%s%n", rs.getString(1));
}
}
Salida de prueba:
roundtrip:
"{""(10.0.0.1,1)"",""(10.0.0.2,2)""}"
"{""(10.10.10.1,80)"",""(10.10.10.2,443)""}"
"{""(10.10.10.3,8080)"",""(10.10.10.4,4040)""}"
json:
[{"addresses":[{"ip":"10.0.0.1","port":1},{"ip":"10.0.0.2","port":2}]},{"addresses":[{"ip":"10.10.10.1","port":80},{"ip":"10.10.10.2","port":443}]},{"addresses":[{"ip":"10.10.10.3","port":8080},{"ip":"10.10.10.4","port":4040}]}]