1
votes

Lire un fichier JSON dans une table

J'essaie de lire des fichiers JSON à partir d'un emplacement et d'écrire dans la table du serveur SQL. Les fichiers à l'emplacement changent tous les jours, il se peut donc que je doive trouver un moyen dynamique de sélectionner le nom du fichier.

J'ai essayé d'utiliser OPENROWSET , mais j'ai lu que je ne peux pas paramétrer le nom de l'emplacement avec cela. J'ai essayé d'utiliser OPENROWSET avec une requête dynamique mais j'obtiens une erreur indiquant que l'emplacement du fichier est introuvable. Après avoir lu à ce sujet, cela pourrait être un problème d'autorisation de dossier.

Ce que je n'essaye pas et j'espère obtenir de l'aide, c'est comment je peux lire le fichier texte JSON et écrire les données dans une table, puis je peux utiliser la fonction OPENJSON à partir de là.

Quelqu'un peut-il m'aider à savoir comment charger un serveur SQL avec les données JSON dans une sorte de blob en utilisant T-SQL ou SSIS?

json sql sql-server tsql ssis

1 commentaires

Vérifiez le lien suivant: stackoverflow.com/questions/56534451/...

3 Réponses :

0
votes

Donc, si je comprends bien, votre problème n'est pas de savoir comment lire le JSON, mais plutôt comment obtenir le fichier?

Comme vous l'avez découvert, toute interaction avec le système de fichiers en dehors de SQL-Server ( T-SQL ) peut devenir très délicat. SQL-Server est limité à son propre utilisateur et verra sa propre machine. Donc, un chemin sur C: \ peut ne pas être celui que vous attendiez.

Cependant, avant de bricoler avec les autorisations, kerberos pour agir comme -authentification et des chemins partagés que je suggérerais de créer une table de préparation comme:

CREATE TABLE dbo.JSONImport_staging
                           (ID INT IDENTITY CONSTRAINT PK_JSONImport_staging PRIMARY KEY
                           ,ImportDate DATETIME2 NOT NULL CONSTRAINT DF_JSONImport_staging_ImportDate DEFAULT(SYSUTCDATETIME())
                           ,FileLocation NVARCHAR(1000) NULL
                           ,Content NVARCHAR(MAX) NULL
                           ,ProcessedOn DATETIME2 NULL
                           ,Success BIT NULL);

Et utilisez l'une des nombreuses approches que vous trouverez sur le net pour stocker des données dans une telle table

PowerShell (quelque chose de le long de ce a >)
Tout langage de programmation de votre choix
SSIS
et bien d'autres

Vous pouvez facilement utiliser une tâche planifiée externe pour rechercher des fichiers et les déplacer dans la table intermédiaire, puis utiliser une tâche interne (dans SQL-Server) pour rechercher les fichiers non traités et les lire dans les tables cible .

Comme toujours dans de tels cas:

Gardez la table de préparation aussi ouverte, générique et tolérante que possible.
Effectuez des vérifications d'intégrité, des conversions et des traitements en toute sécurité sur le chemin entre votre table intermédiaire et les tables cible.

2 commentaires

Correct. J'ai vérifié sur le net comment stocker des données dans la table où j'ai eu du mal. Je suis limité à l'utilisation de SSIS et TSQL. J'aime l'idée de la table de mise en scène. J'ai utilisé cela combiné avec SSIS Import Column, mais ma colonne de contenu (en utilisant vos noms de colonne) contient des caractères HEX. Pas ce à quoi je m'attendais.

@BRILLIANT Si le Contenu est NVARCHAR (MAX) , vous pouvez y lire directement un fichier de chaîne. Assurez-vous que vous gérez correctement les encodages (utf-8, utf-16 et bien d'autres). Si vous voyez des codes HEX (et que vous avez défini Contenu comme VARBINARY (MAX) ), vous pouvez essayer de les convertir en VARCHAR (MAX) ou NVARCHAR (MAX) . Mais c'est dangereux. SQL-Server est très limité avec les encodages ... À partir de la v2014 SP2, il y a importation utf-8 avec BULK ...

0
votes

Assurez-vous qu'un fichier est accessible par SQL

IF OBJECT_ID('tempdb..#JsonFile') IS NOT NULL
DROP TABLE #JsonFile;

CREATE TABLE #JsonFile
    (
        [JsonLine] NVARCHAR(MAX)
    );

BULK INSERT #JsonFile
FROM '\\UNC_path\file.json'
WITH ( ROWTERMINATOR = '' );

SELECT *
FROM   #JsonFile;

1 commentaires

Vous devriez vraiment lire JSON dans NVARCHAR (MAX) ... Outre le fait, l'accès aux fichiers depuis T-SQL en utilisant BULK peut obtenir vraiment moche, vous devrez utiliser quelques paramètres supplémentaires comme CODEPAGE pour obtenir un résultat correct ...

0
votes

--Bulk Importer des données depuis le fichier

Insert into TestDetails(Active,CreatedDate,testCode,Test,Method)
SELECT '1','2019-10-23 06:01:10.7927233',testCode,Test,Method FROM 
OPENJSON(@TestDetails, '$.Tests')
WITH(
testCode nvarchar(50)'$.testCode',
Test nvarchar(50)'$.Test',
Method nvarchar(50)'$.Method'
)[Json file Screen Shot][1]

--Afficher les données importées depuis Bulk Import comme une seule colonne

SELECT testCode,Test,Method FROM OPENJSON(@TestDetails, '$.Tests')
WITH(
testCode nvarchar(50)'$.testCode',
Test nvarchar(50)'$.Test',
Method nvarchar(50)'$.Method'
)

- -Vérifiez si les données importées sont des données valides ou non si = 1 les données sont valides

if(ISJSON(@TestDetails)=1)
BEGIN
PRINT 'Valid Data Imported'
END
ELSE
BEGIN
PRINT 'Invalid Data Imported'
END
GO

- Sélectionnez maintenant les données à ajouter à la table ici $ .tests est le nom de l'objet du tableau

DECLARE @TestDetails VARCHAR(MAX)
SELECT @TestDetails = BulkColumn FROM 
OPENROWSET(BULK'D:\Omkar\Projects\HS\Documents\test.json', SINGLE_BLOB) JSON;

SELECT @TestDetails as SingleRow_Column

- Insérez maintenant des données dans la table si des valeurs par défaut doivent être insérées puis dans select prendre la valeur par défaut

Select BulkColumn from openrowset(Bulk'D:\home\HS\HS-Web\wwwroot\Json files\test.json',single_blob)JSON;

0 commentaires