Configurer des sources de données externes pour les utiliser avec PolyBase

MySQL | PostgreSQL | SQL Server

Cette page vous explique comment configurer et gérer des sources de données externes à utiliser avec PolyBase et vos instances Cloud SQL pour SQL Server.

Gérer l'accès aux objets externes

Cloud SQL n'est compatible qu'avec les opérations permettant d'activer et de désactiver PolyBase. Les utilisateurs doivent gérer manuellement les entités SQL Server à l'aide d'instructions T-SQL.

Avant de gérer manuellement vos entités SQL Server, consultez les ressources Microsoft suivantes :

Créer une source de données externe

Les étapes suivantes vous montrent comment créer une source de données externe pour votre instance Cloud SQL pour SQL Server.

SQL Server définit une source de données externe comme une ressource contenant des informations de connexion telles que le nom du serveur, le nom de la base de données et la chaîne de connexion.

SQL Server définit les tables externes comme des objets de métadonnées dans SQL Server qui pointent vers les données des sources de données externes. Ils définissent le schéma des données externes telles qu'elles seront présentées dans SQL Server.

Cette procédure nécessite la création d'un identifiant à portée de base de données (DSC) pour votre source de données externe.

Les DSC stockent les informations d'authentification nécessaires pour se connecter aux sources de données externes. Ces identifiants sont chiffrés par la clé principale de la base de données (DMK).

La DMK sert de racine à la hiérarchie de chiffrement dans une base de données, protégeant les secrets dans les identifiants à portée de base de données. Cette clé symétrique est chiffrée par la clé principale du service (SMK, Service Master Key) et un mot de passe fourni par l'utilisateur. La clé DMK n'est pas créée automatiquement et nécessite une gestion explicite par l'utilisateur à l'aide de T-SQL.

La SMK sert de racine à la hiérarchie de chiffrement pour une instance spécifique de SQL Server. Cette clé symétrique est générée automatiquement par SQL Server lui-même lors de son premier démarrage. Elle est utilisée pour protéger d'autres objets de sécurité tels que les clés principales de base de données et les mots de passe de connexion.

Oracle

Effectuez les étapes suivantes dans SQL Server.

Créez des identifiants à portée de base de données pour Oracle.
1. S'il n'en existe pas déjà, passez à une base de données utilisateur pour créer une clé principale :
```
USE USER_DB;
CREATE MASTER KEY ENCRYPTION BY PASSWORD='MK_PASSWORD';
```
  Remplacez les éléments suivants :
  - USER_DB : base de données utilisateur que vous souhaitez utiliser pour créer la clé principale. Exemple :polybasedb
  - MK_PASSWORD : mot de passe de la clé principale que vous souhaitez utiliser. Veillez à enregistrer le mot de passe de la clé principale pour une utilisation ultérieure.
2. Créez un identifiant à l'échelle de la base de données pour la source de données Oracle.
```
CREATE DATABASE SCOPED CREDENTIAL DB_CREDENTIAL_NAME WITH IDENTITY = 'EXTERNAL_DATABASE_USERNAME', SECRET = 'EXTERNAL_DATABASE_PASSWORD';
```
  Remplacez les éléments suivants :
  - DB_CREDENTIAL_NAME : nom de l'identifiant que vous souhaitez utiliser pour la base de données externe.
  - EXTERNAL_DATABASE_USERNAME : nom d'utilisateur que vous souhaitez utiliser pour accéder à la base de données externe.
  - EXTERNAL_DATABASE_PASSWORD : mot de passe de la base de données externe. Vous devez mettre à jour manuellement les identifiants de la source de données externe lorsqu'ils sont modifiés.
Créez une source de données externe pour Oracle.
```
CREATE EXTERNAL DATA SOURCE DATA_SOURCE_NAME
WITH (
    LOCATION = 'oracle://SERVER_IP:PORT',
    PUSHDOWN = PUSHDOWN,
    CREDENTIAL = DB_CREDENTIAL_NAME
);
```
Remplacez les éléments suivants :
- DATA_SOURCE_NAME : nom que vous souhaitez utiliser pour la base de données externe.
- SERVER_IP : adresse IP ou nom d'hôte du serveur.
- PORT : port que vous souhaitez utiliser.
- PUSHDOWN : valeur obligatoire. Le pushdown est une fonctionnalité proposée dans SQL Server et doit être configurée. Définissez la valeur sur ON ou OFF, selon votre cas d'utilisation.
- DB_CREDENTIAL_NAME : identifiant que vous venez de créer.
Créez une table externe.

Définissez le schéma et la structure des données externes :
- Créez la définition de la table externe. Le schéma doit correspondre à la table distante.
- Définissez les colonnes avec les types de données et les classements SQL Server appropriés.
- L'emplacement correspond au chemin d'accès à la table distante.
```
CREATE EXTERNAL TABLE EXTERNAL_TABLE_NAME(
    COLUMN_NAME_1 DATA_TYPE_1 COLLATE COLLATION_1,
    COLUMN_NAME_2 DATA_TYPE_2 COLLATE COLLATION_2,
    ...
)
WITH(
    LOCATION = 'REMOTE_DATABASE.REMOTE_USERNAME.REMOTE_TABLE_NAME',
    DATA_SOURCE = DB_DATA_SOURCE_NAME
);
```
Remplacez les éléments suivants :
- EXTERNAL_TABLE_NAME : nom de la table externe que vous souhaitez créer.
- COLUMN_NAME_1 : nom de la première colonne du tableau.
- DATA_TYPE_1 : type de données de la première colonne.
- COLLATION_1 : collation que vous souhaitez utiliser pour la première colonne.
- COLUMN_NAME_2 : nom de la deuxième colonne du tableau.
- DATA_TYPE_2 : type de données de la deuxième colonne.
- COLLATION_2 : collation que vous souhaitez utiliser pour la deuxième colonne.
- REMOTE_DATABASE : nom de la base de données distante.
- REMOTE_USERNAME : nom d'utilisateur de l'utilisateur distant.
- REMOTE_TABLE_NAME : nom de la table distante.
- DB_DATA_SOURCE_NAME : nom de la source de données externe.
Interrogez la table Oracle externe que vous avez créée.
```
SELECT TOP 10 * FROM EXTERNAL_TABLE_NAME;
```
Remplacez les éléments suivants :
- EXTERNAL_TABLE_NAME : nom de la table externe que vous souhaitez interroger.

Cloud Storage

Effectuez les étapes suivantes dans SQL Server.

Créez un identifiant à portée de base de données pour Cloud Storage.
1. S'il n'en existe pas déjà, passez à une base de données utilisateur pour créer une clé principale :
```
USE USER_DB;
CREATE MASTER KEY ENCRYPTION BY PASSWORD='MK_PASSWORD';
```
  Remplacez les éléments suivants :
  - USER_DB : base de données utilisateur que vous souhaitez utiliser pour créer la clé principale. Exemple :polybasedb
  - MK_PASSWORD : mot de passe que vous souhaitez utiliser pour la clé principale. Veillez à enregistrer le mot de passe de la clé principale pour une utilisation ultérieure.
2. Cloud Storage nécessite un ID de clé d'accès et un ID de clé secrète :
```
CREATE DATABASE SCOPED CREDENTIAL STORAGE_CREDENTIAL_NAME WITH IDENTITY = 'S3 Access Key', SECRET = 'ACCESS_KEY_ID:SECRET_KEY_ID';
```
  Remplacez les éléments suivants :
  - STORAGE_CREDENTIAL_NAME : nom des identifiants Cloud Storage que vous souhaitez utiliser.
  - ACCESS_KEY_ID : ID de la clé d'accès.
  - SECRET_KEY_ID : ID de la clé secrète.
  Seule l'authentification de base est acceptée.
Créez une source de données externe.
```
CREATE EXTERNAL DATA SOURCE STORAGE_DATA_SOURCE_NAME
WITH (
    LOCATION = 's3://storage.googleapis.com/',
    CREDENTIAL = STORAGE_CREDENTIAL_NAME
);
```
Remplacez les éléments suivants :
- STORAGE_DATA_SOURCE_NAME : nom de la source de données externe dans Cloud Storage.
- STORAGE_CREDENTIAL_NAME : nom de l'identifiant pour la ressource de stockage externe.
Créez une table externe pour Cloud Storage.

Définissez le format et la structure des données externes :
- Créez le fichier externe. Le schéma doit correspondre à la structure du fichier.
- Définissez les colonnes avec les types de données et les classements SQL Server appropriés.
- L'emplacement correspond au chemin d'accès dans la source de données.
```
CREATE EXTERNAL FILE FORMAT FILE_FORMAT_NAME
WITH (
    FORMAT_TYPE = FORMAT_TYPE,
    FORMAT_OPTIONS (
        FIELD_TERMINATOR = 'FIELD_TERMINATOR',
        STRING_DELIMITER = 'DELIMITER',
        FIRST_ROW = FIRST_ROW
    )
);

CREATE EXTERNAL TABLE FILE_EXTERNAL_TABLE_NAME (
    COLUMN_NAME_1 DATA_TYPE_1,
    COLUMN_NAME_2 DATA_TYPE_2,
    ...
)
WITH (
    LOCATION = 'PATH_TO_BUCKET/FILENAME',
    DATA_SOURCE = STORAGE_DATA_SOURCE_NAME,
    FILE_FORMAT = FILE_FORMAT_NAME
);
```
Remplacez les éléments suivants :
- FILE_FORMAT_NAME : nom du format de fichier externe que vous souhaitez créer.
- FORMAT_TYPE : type de format que vous souhaitez utiliser, tel que DELIMITEDTEXT. Pour obtenir la liste des valeurs acceptées, consultez Formats de fichiers acceptés.
- FIELD_TERMINATOR : délimiteur de champ que vous souhaitez utiliser.
- DELIMITER : délimiteur de chaîne que vous souhaitez utiliser.
- FIRST_ROW : ligne à partir de laquelle vous souhaitez commencer la lecture.
- FILE_EXTERNAL_TABLE_NAME : nom de la table externe que vous souhaitez créer.
- COLUMN_NAME_1 : nom de la première colonne du tableau.
- DATA_TYPE_1 : type de données de la première colonne.
- COLUMN_NAME_2 : nom de la deuxième colonne du tableau.
- DATA_TYPE_2 : type de données de la deuxième colonne.
- PATH_TO_BUCKET : chemin d'accès complet au bucket Cloud Storage.
- FILENAME : nom du fichier situé dans le bucket Cloud Storage.
- STORAGE_DATA_SOURCE_NAME : nom de la source de données externe dans Cloud Storage.
- FILE_FORMAT_NAME : nom du format de fichier externe.
Interrogez la table Cloud Storage externe que vous avez créée.
```
SELECT * FROM FILE_EXTERNAL_TABLE_NAME;
```
Remplacez les éléments suivants :
- FILE_EXTERNAL_TABLE_NAME : nom de la table externe que vous souhaitez interroger.

Gérer la clé principale de la base de données après une opération de restauration de base de données

Les identifiants à l'échelle de la base de données utilisés pour accéder aux sources de données externes sont chiffrés à l'aide d'une clé principale de base de données (DMK).

La DMK est chiffrée avec la clé principale du service (SMK) de l'instance source et le mot de passe DMK fourni par l'utilisateur. Sur l'instance source, SQL Server ouvre le DMK en mode silencieux à l'aide d'un SMK.

Si vous restaurez une instance Cloud SQL pour SQL Server, la clé DMK ne s'ouvrira pas automatiquement sur la nouvelle instance en raison de modifications apportées à la clé SMK.

Par conséquent, vous devez d'abord déchiffrer la clé DMK à l'aide du mot de passe utilisateur défini lors de sa création, puis la rechiffrer avec la clé SMK de l'instance cible.

Pour en savoir plus, consultez les ressources Microsoft suivantes :

Journaux

Les journaux PolyBase suivants sont disponibles dans l'explorateur de journaux :

Polybase_Dms_errors.log
Polybase_Dms_movement.log
Polybase_DWEngine_errors.log
Polybase_DWEngine_movement.log
Polybase_DWEngine_server.log

Pour en savoir plus, consultez la documentation Microsoft sur le dépannage de PolyBase.

Configurer des sources de données externes pour les utiliser avec PolyBase Restez organisé à l'aide des collections Enregistrez et classez les contenus selon vos préférences.