Traiter des données non structurées en utilisant des UDFs ou des UDTFs Java

Cette rubrique fournit des exemples sur comment lire et traiter des données non structurées dans des fichiers en zone de préparation en utilisant des UDFs Java (fonctions définies par l’utilisateur) ou des UDFs tabulaires Java (fonctions de table définies par l’utilisateur).

Pour plus d’informations sur l’utilisation de Java pour développer des gestionnaires d’UDF, voir ce qui suit :

Pour plus d’informations sur la lecture d’un fichier avec le code du gestionnaire d’UDF Java, voir Lire un fichier en utilisant IMPORTS.

Note

Pour rendre votre code résistant aux attaques par injection de fichiers, utilisez toujours une URL scopée lorsque vous transmettez l’emplacement d’un fichier à une UDF, en particulier lorsque l’appelant de la fonction n’est pas également son propriétaire. Vous pouvez créer une URL scopée en SQL à l’aide de la fonction intégrée build_scoped_file_url. Pour plus d’informations sur le rôle de build_scoped_file_url, voir Présentation de la prise en charge des données non structurées.

Dans ce chapitre :

Exemples d’UDF Java

L’exemple de cette section traite des fichiers non structurés en zone de préparation à l’aide d’UDFs Java, qui extrait et renvoie le texte des fichiers.

Conditions préalables : créer des zones de préparation

Les exemples utilisent des UDFs dont le code de gestionnaire est en ligne (et non compilé dans un JAR en zone de préparation), ce qui signifie que vous ne devez pas compiler, empaqueter ni télécharger le code Java de votre UDF dans une zone de préparation. Pour plus d’informations sur les gestionnaires en ligne et en zone de préparation, voir Conserver le code du gestionnaire en ligne ou dans une zone de préparation.

Cependant, les exemples dépendent d’une bibliothèque séparée qui est empaquetée dans un fichier JAR. Vous devez télécharger le fichier JAR de cette bibliothèque sur une zone de préparation. Les exemples utilisent une zone de préparation interne pour stocker le fichier JAR de cette bibliothèque.

Bien que les fichiers de données non structurés traités par une UDF Java puissent être situés dans la même zone de préparation que les fichiers JAR, dans ces exemples, les fichiers de données sont situés dans une zone de préparation interne distincte.

Créez les zones de préparation en utilisant un rôle qui dispose des privilèges minimums requis, comme décrit dans Accorder des privilèges pour les fonctions définies par l’utilisateur.

Les instructions SQL suivantes créent des zones de préparation internes distinctes pour stocker séparément les fichiers JAR et les fichiers de données pour les exemples :

-- Create an internal stage to store the JAR files.
CREATE OR REPLACE STAGE jars_stage;

-- Create an internal stage to store the data files. The stage includes a directory table.
CREATE OR REPLACE STAGE data_stage DIRECTORY=(ENABLE=TRUE) ENCRYPTION = (TYPE='SNOWFLAKE_SSE');
Copy

Traiter des fichiers PDF

Cet exemple extrait le contenu d’un fichier PDF spécifié en utilisant Apache PDFBox.

Effectuez les étapes suivantes pour créer l’UDF Java et télécharger les fichiers requis :

  1. Copiez le fichier JAR pour Apache PDFBox du répertoire temporaire local vers la zone de préparation qui stocke les fichiers JAR :

    Linux/Mac
    PUT file:///tmp/pdfbox-app-2.0.27.jar @jars_stage AUTO_COMPRESS=FALSE;
    
    Copy
    Windows
    PUT file://C:\temp\pdfbox-app-2.0.27.jar @jars_stage AUTO_COMPRESS=FALSE;
    
    Copy
  2. Créez une UDF Java pour analyser des documents PDF et récupérer le contenu de chaque document. Vous pouvez utiliser soit la classe SnowflakeFile, soit la classe InputStream dans le code de votre UDF :

    Utilisation de la classe SnowflakeFile
    CREATE FUNCTION process_pdf(file string)
    RETURNS string
    LANGUAGE java
    RUNTIME_VERSION = 11
    IMPORTS = ('@jars_stage/pdfbox-app-2.0.27.jar')
    HANDLER = 'PdfParser.readFile'
    as
    $$
    import org.apache.pdfbox.pdmodel.PDDocument;
    import org.apache.pdfbox.text.PDFTextStripper;
    import org.apache.pdfbox.text.PDFTextStripperByArea;
    import com.snowflake.snowpark_java.types.SnowflakeFile;
    
    import java.io.File;
    import java.io.FileInputStream;
    import java.io.IOException;
    import java.io.InputStream;
    
    public class PdfParser {
    
        public static String readFile(String fileURL) throws IOException {
            SnowflakeFile file = SnowflakeFile.newInstance(fileURL);
            try (PDDocument document = PDDocument.load(file.getInputStream())) {
    
                document.getClass();
    
                if (!document.isEncrypted()) {
    
                    PDFTextStripperByArea stripper = new PDFTextStripperByArea();
                    stripper.setSortByPosition(true);
    
                    PDFTextStripper tStripper = new PDFTextStripper();
    
                    String pdfFileInText = tStripper.getText(document);
                    return pdfFileInText;
                }
            }
    
            return null;
        }
    }
    $$;
    
    Copy
    Utilisation de la classe InputStream
    CREATE FUNCTION process_pdf(file string)
    RETURNS string
    LANGUAGE java
    RUNTIME_VERSION = 11
    IMPORTS = ('@jars_stage/pdfbox-app-2.0.27.jar')
    HANDLER = 'PdfParser.readFile'
    as
    $$
    import org.apache.pdfbox.pdmodel.PDDocument;
    import org.apache.pdfbox.text.PDFTextStripper;
    import org.apache.pdfbox.text.PDFTextStripperByArea;
    
    import java.io.File;
    import java.io.FileInputStream;
    import java.io.IOException;
    import java.io.InputStream;
    
    public class PdfParser {
    
        public static String readFile(InputStream stream) throws IOException {
            try (PDDocument document = PDDocument.load(stream)) {
    
                document.getClass();
    
                if (!document.isEncrypted()) {
    
                    PDFTextStripperByArea stripper = new PDFTextStripperByArea();
                    stripper.setSortByPosition(true);
    
                    PDFTextStripper tStripper = new PDFTextStripper();
    
                    String pdfFileInText = tStripper.getText(document);
                    return pdfFileInText;
                }
            }
            return null;
        }
    }
    $$;
    
    Copy
  3. Copiez le fichier PDF du répertoire temporaire local vers la zone de préparation qui stocke les fichiers de données :

    Linux/Mac
    PUT file:///tmp/myfile.pdf @data_stage AUTO_COMPRESS=FALSE;
    
    Copy
    Windows
    PUT file://C:\temp\myfile.pdf @data_stage AUTO_COMPRESS=FALSE;
    
    Copy
  4. Actualisez la table du répertoire pour la zone de préparation data_stage :

    ALTER STAGE data_stage REFRESH;
    
    Copy
  5. Appelez l’UDF Java pour lire un ou plusieurs fichiers PDF en zone de préparation et en extraire le contenu.

    Le code de l’exemple suivant appelle l’UDF, en lui passant une URL scopée pour rendre le code résistant aux attaques par injection de fichier. Vous devez toujours utiliser une URL scopée lorsque l’appelant de la fonction n’est pas également son propriétaire. Vous pouvez passer l’argument URL sous la forme d’une URL scopée ou sous une autre forme lorsque l’appelant de l’UDF est également son propriétaire.

    -- Input a scoped URL.
    SELECT process_pdf(build_scoped_file_url('@data_stage', '/myfile.pdf'));
    
    Copy

Exemples d’UDTF Java

L’exemple de cette section extrait et renvoie des données à partir de fichiers en zone de préparation en utilisant des UDTFs Java.

Conditions préalables : Créer une zone de préparation de données

Créez une zone de préparation pour stocker vos fichiers de données à l’aide d’un rôle qui dispose des privilèges minimums requis, comme décrit dans Accorder des privilèges pour les fonctions définies par l’utilisateur.

L’instruction SQL suivante crée une zone de préparation interne pour stocker les fichiers de données de l’exemple :

-- Create an internal stage to store the data files. The stage includes a directory table.
CREATE OR REPLACE STAGE data_stage DIRECTORY=(ENABLE=TRUE) ENCRYPTION = (TYPE='SNOWFLAKE_SSE');
Copy

Traiter des fichiers CSV

Cet exemple extrait le contenu d’un ensemble spécifié de fichiers CSV et renvoie les lignes dans une table.

Effectuez les étapes suivantes pour créer l’UDTF Java et télécharger les fichiers requis :

  1. Créez une UDTF Java qui utilise la classe SnowflakeFile :

    CREATE OR REPLACE FUNCTION parse_csv(file string)
    RETURNS TABLE (col1 string, col2 string, col3 string )
    LANGUAGE JAVA
    HANDLER = 'CsvParser'
    as
    $$
    import org.xml.sax.SAXException;
    
    import java.io.*;
    import java.util.ArrayList;
    import java.util.List;
    import java.util.stream.Stream;
    import com.snowflake.snowpark_java.types.SnowflakeFile;
    
    public class CsvParser {
        public class Record {
            public String col1;
            public String col2;
            public String col3;
    
            public Record(String col1_value, String col2_value, String col3_value)
            {
                col1 = col1_value;
                col2 = col2_value;
                col3 = col3_value;
            }
        }
    
        public static Class getOutputClass() {
            return Record.class;
        }
    
        public Stream<Record> process(String file_url) throws IOException {
            SnowflakeFile file = SnowflakeFile.newInstance(file_url);
    
            String csvRecord = null;
            List<Record> rows = new ArrayList<>();
            BufferedReader csvReader = null;
    
            try {
                csvReader = new BufferedReader(new InputStreamReader(file.getInputStream()));
                while ((csvRecord = csvReader.readLine()) != null) {
                    String[] columns = csvRecord.split(",", 3);
                    rows.add(new Record(columns[0], columns[1], columns[2]));
                }
            } catch (IOException e) {
                throw new RuntimeException("Reading CSV failed.", e);
            } finally {
                if (csvReader != null)
                    try {
                        csvReader.close();
                    } catch (IOException e) {
                        e.printStackTrace();
                    }
            }
    
            return rows.stream();
        }
    }
    $$
    ;
    
    Copy
  2. Copiez le fichier CSV du répertoire temporaire local vers la zone de préparation qui stocke les fichiers de données :

    Linux/Mac
    PUT file:///tmp/sample.csv @data_stage AUTO_COMPRESS=FALSE;
    
    Copy
    Windows
    PUT file://C:\temp\sample.csv @data_stage AUTO_COMPRESS=FALSE;
    
    Copy
  3. Actualisez la table du répertoire pour la zone de préparation data_stage :

    ALTER STAGE data_stage REFRESH;
    
    Copy
  4. Appelez l’UDTF Java pour lire un ou plusieurs fichiers CSV en zone de préparation et extraire le contenu sous forme de table :

    Le code de l’exemple suivant appelle l’UDF, en transmettant une URL scopée pour réduire le risque d’attaques par injection de fichiers. Vous devez toujours utiliser une URL scopée lorsque l’appelant de la fonction n’est pas également son propriétaire. Vous pouvez passer l’argument URL sous la forme d’une URL scopée ou sous une autre forme prise en charge lorsque l’appelant de l’UDF est également son propriétaire.

    -- Input a file URL.
    SELECT * FROM TABLE(PARSE_CSV(BUILD_SCOPED_FILE_URL(@data_stage, 'sample.csv')));
    
    Copy