I’m currently working on a project where I need to create a table in GCP BigQuery. My goal is to read the schema directly from a JSON file and use it to generate the SQL command for table creation. Below is the code I’ve been working with:
config {
type: "table",
name: "test",
description: "Table interne créée à partir de la table externe avec types de données corrigés"
}
js {
const schemaData = {
"fields": [
{"name": "NO_CNT", "type": "STRING", "size": 7, "description": "Numéro de contrat"},
{"name": "NO_LIG_CNT", "type": "INTEGER", "description": "Numéro de ligne de contrat"},
{"name": "NO_ECH_FACT", "type": "INTEGER", "description": "Numéro d'échéance de facture"},
{"name": "NO_EVT", "type": "STRING", "size": 30, "description": "Numéro d'événement"},
{"name": "DT_ECH", "type": "DATE", "description": "Date d'échéance"},
{"name": "DTD_PERFAC", "type": "DATE", "description": "Date de début de période de facturation"},
{"name": "DTF_PERFAC", "type": "DATE", "description": "Date de fin de période de facturation"},
{"name": "MT_ECH", "type": "INTEGER", "description": "Montant de l'échéance"},
{"name": "MT_TVA_LIG_ECH", "type": "INTEGER", "description": "Montant TVA ligne échéance"},
{"name": "MT_INT_ECH", "type": "INTEGER", "description": "Montant intérêt échéance"},
{"name": "CD_STA_ECH", "type": "INTEGER", "description": "Code statut échéance"},
{"name": "DT_STA_ECH", "type": "DATE", "description": "Date statut échéance"},
{"name": "CD_MOT_STA_ECH", "type": "STRING", "size": 20, "description": "Code motif statut échéance"},
{"name": "CD_REGR_FACT", "type": "STRING", "size": 20, "description": "Code regroupement facture"},
{"name": "CD_TY_ECH", "type": "INTEGER", "description": "Code type échéance"},
{"name": "DT_BLQ_ECH", "type": "DATE", "description": "Date blocage échéance"},
{"name": "MT_ENC_FN_PER", "type": "INTEGER", "description": "Montant encaissé fin période"},
{"name": "DT_CRE", "type": "DATE", "description": "Date de création"},
{"name": "DT_MAJ", "type": "DATE", "description": "Date de mise à jour"},
{"name": "LOG_COUNTER", "type": "INTEGER", "description": "Compteur de log"},
{"name": "ID_COLLA_MAJ", "type": "STRING", "size": 30, "description": "ID collaborateur mise à jour"},
{"name": "CD_PROD", "type": "STRING", "size": 20, "description": "Code produit"},
{"name": "CD_OBJ_VAR", "type": "INTEGER", "description": "Code objet variable"},
{"name": "DT_TRT", "type": "DATE", "description": "Date de traitement"}
]
};
// Fonction pour générer la commande SQL pour créer la table
function generateCreateTableSQL() {
let createTableSQL = "CREATE TABLE `dwz_dev.test` (\n"; // Utilisez le bon nom de table
schemaData.fields.forEach(field => {
let fieldSQL = `${field.name} ${field.type}`;
if (field.type === 'STRING' && field.size) {
fieldSQL += `(${field.size})`; // Ajouter la taille pour les colonnes STRING
}
fieldSQL += ` -- ${field.description}\n`; // Ajouter la description de chaque colonne
createTableSQL += fieldSQL;
});
createTableSQL += ");"; // Terminer la commande CREATE TABLE
return createTableSQL;
}
// Générer le SQL pour la création de la table
const createTableSQL = generateCreateTableSQL();
// Output the SQL statement
return createTableSQL; // Return the SQL statement to be used later
}
I have this error:
Syntax error: Unexpected identifier "C" at [1:1]
However, I’m encountering syntax errors and I’m not sure if my approach is correct. If anyone has experience with reading schemas from JSON files for table creation in BigQuery, I would greatly appreciate your guidance. If this method isn’t feasible, I’ll consider specifying the schema directly in the code.
Thank you in advance for your help!
Best regards,
Dave
LinkedIn
Twitter
