Tutorial: Carga de conjuntos de datos de taxis de Nueva YorkTutorial: Load the New York Taxicab dataset

En este tutorial se usa la instrucción COPY para cargar un conjunto de datos de taxis de Nueva York desde una cuenta de Azure Blob Storage.This tutorial uses the COPY statement to load New York Taxicab dataset from an Azure Blob Storage account. El tutorial utiliza Azure Portal y SQL Server Management Studio (SSMS) para:The tutorial uses the Azure portal and SQL Server Management Studio (SSMS) to:

  • Crear un usuario designado para cargar datosCreate a user designated for loading data
  • Crear tablas para el conjunto de datos de ejemploCreate the tables for the sample dataset
  • Usar la instrucción COPY de T-SQL para cargar datos en el almacenamiento de datosUse the COPY T-SQL statement to load data into your data warehouse
  • Ver el progreso de los datos a medida que se carganView the progress of data as it is loading

Si no tiene una suscripción a Azure, cree una cuenta gratuita antes de empezar.If you don't have an Azure subscription, create a free account before you begin.

Antes de empezarBefore you begin

Antes de completar este tutorial, descargue e instale la versión más reciente de SQL Server Management Studio (SSMS).Before you begin this tutorial, download and install the newest version of SQL Server Management Studio (SSMS).

En este tutorial se supone que ya ha creado un grupo dedicado de SQL a partir del siguiente tutorial.This tutorial assumes you have already created a SQL dedicated pool from the following tutorial.

Creación de un usuario para cargar datosCreate a user for loading data

La cuenta de administrador del servidor está pensada para realizar operaciones de administración y no es adecuada para ejecutar consultas en datos de usuario.The server admin account is meant to perform management operations, and is not suited for running queries on user data. La carga de datos es una operación que utiliza mucha memoria.Loading data is a memory-intensive operation. Los valores máximos de memoria se definen por las unidades de almacenamiento de datos y la clase de recurso configuradas.Memory maximums are defined according to the data warehouse units and resource class configured.

Es mejor crear un inicio de sesión y un usuario que esté dedicado para cargar datos.It's best to create a login and user that is dedicated for loading data. A continuación, agregue el usuario de carga a una clase de recurso que permita una asignación de memoria máxima apropiada.Then add the loading user to a resource class that enables an appropriate maximum memory allocation.

Conéctese como administrador del servidor para que pueda crear inicios de sesión y usuarios.Connect as the server admin so you can create logins and users. Siga estos pasos para crear un inicio de sesión y un usuario llamado LoaderRC20.Use these steps to create a login and user called LoaderRC20. Luego, asigne el usuario a la clase de recurso staticrc20.Then assign the user to the staticrc20 resource class.

  1. En SSMS, haga clic con el botón derecho en maestro para mostrar un menú desplegable y elija Nueva consulta.In SSMS, right-select master to show a drop-down menu, and choose New Query. Se abrirá una nueva ventana de consulta.A new query window opens.

    Nueva consulta en maestro

  2. En la ventana de consulta, escriba estos comandos de T-SQL para crear un inicio de sesión y un usuario denominado LoaderRC20, sustituyendo su propia contraseña por 'a123STRONGpassword!'.In the query window, enter these T-SQL commands to create a login and user named LoaderRC20, substituting your own password for 'a123STRONGpassword!'.

    CREATE LOGIN LoaderRC20 WITH PASSWORD = 'a123STRONGpassword!';
    CREATE USER LoaderRC20 FOR LOGIN LoaderRC20;
    
  3. Seleccione Execute(Ejecutar).Select Execute.

  4. Haga clic con el botón derecho en mySampleDataWarehouse y elija Nueva consulta.Right-click mySampleDataWarehouse, and choose New Query. Se abre una nueva ventana de consulta.A new query Window opens.

    Nueva consulta en el almacenamiento de datos de ejemplo

  5. Escriba los siguientes comandos de T-SQL para crear un usuario de base de datos denominado LoaderRC20 para el inicio de sesión LoaderRC20.Enter the following T-SQL commands to create a database user named LoaderRC20 for the LoaderRC20 login. La segunda línea concede al nuevo usuario permisos de CONTROL en el nuevo almacenamiento de datos.The second line grants the new user CONTROL permissions on the new data warehouse. Estos permisos son similares a convertir al usuario en el propietario de la base de datos.These permissions are similar to making the user the owner of the database. La tercera línea agrega el nuevo usuario como miembro de la clase de recurso staticrc20.The third line adds the new user as a member of the staticrc20 resource class.

    CREATE USER LoaderRC20 FOR LOGIN LoaderRC20;
    GRANT CONTROL ON DATABASE::[mySampleDataWarehouse] to LoaderRC20;
    EXEC sp_addrolemember 'staticrc20', 'LoaderRC20';
    
  6. Seleccione Execute(Ejecutar).Select Execute.

Conexión al servidor como el usuario de cargaConnect to the server as the loading user

El primer paso para cargar datos es iniciar sesión como LoaderRC20.The first step toward loading data is to login as LoaderRC20.

  1. En el Explorador de objetos, seleccione el menú desplegable Conectar y, a continuación, Motor de base de datos.In Object Explorer, select the Connect drop down menu and select Database Engine. Aparecerá el cuadro de diálogo Conectar con el servidor .The Connect to Server dialog box appears.

    Conexión con nuevo inicio de sesión

  2. Escriba el nombre completo del servidor y escriba LoaderRC20 como inicio de sesión.Enter the fully qualified server name, and enter LoaderRC20 as the Login. Escriba la contraseña para LoaderRC20.Enter your password for LoaderRC20.

  3. Seleccione Conectar.Select Connect.

  4. Cuando la conexión esté lista, verá dos conexiones de servidor en el Explorador de objetos.When your connection is ready, you will see two server connections in Object Explorer. Una conexión como ServerAdmin y otra como MedRCLogin.One connection as ServerAdmin and one connection as MedRCLogin.

    Conexión correcta

Creación de tablas para los datos de ejemploCreate tables for the sample data

Está listo para comenzar el proceso de carga de datos en el nuevo almacenamiento de datos.You are ready to begin the process of loading data into your new data warehouse. En esta parte del tutorial se muestra cómo usar la instrucción COPY para cargar el conjunto de datos de taxis de la ciudad de Nueva York desde un blob de Azure Storage.This part of the tutorial shows you how to use the COPY statement to load the New York City taxi cab dataset from an Azure Storage blob. Para consultas futuras y aprender cómo introducir los datos en Azure Blob Storage o cómo cargarlos directamente desde su origen, consulte la introducción a la carga.For future reference, to learn how to get your data to Azure Blob Storage or to load it directly from your source, see the loading overview.

Ejecute los siguientes scripts SQL para especificar información acerca de los datos que desea cargar.Run the following SQL scripts and specify information about the data you wish to load. Esta información incluye dónde se encuentran los datos, el formato del contenido de los mismos y la definición de tabla para ellos.This information includes where the data is located, the format of the contents of the data, and the table definition for the data.

  1. En la sección anterior, inició sesión en el almacenamiento de datos como LoaderRC20.In the previous section, you logged into your data warehouse as LoaderRC20. En SSMS, haga clic con el botón derecho en la conexión de LoaderRC20 y seleccione Nueva consulta.In SSMS, right-click your LoaderRC20 connection and select New Query. Aparece una nueva ventana de consulta.A new query window appears.

    Nueva ventana de consulta de carga

  2. Compare la ventana de consulta con la imagen anterior.Compare your query window to the previous image. Verifique que la nueva ventana de consulta se ejecuta como LoaderRC20 y que realiza consultas en la base de datos MySampleDataWarehouse.Verify your new query window is running as LoaderRC20 and performing queries on your MySampleDataWarehouse database. Utilice esta ventana de consulta para realizar todos los pasos de carga.Use this query window to perform all of the loading steps.

  3. Ejecute las siguientes instrucciones de T-SQL para crear las tablas:Run the following T-SQL statements to create the tables:

    CREATE TABLE [dbo].[Date]
    (
        [DateID] int NOT NULL,
        [Date] datetime NULL,
        [DateBKey] char(10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [DayOfMonth] varchar(2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [DaySuffix] varchar(4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [DayName] varchar(9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [DayOfWeek] char(1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [DayOfWeekInMonth] varchar(2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [DayOfWeekInYear] varchar(2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [DayOfQuarter] varchar(3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [DayOfYear] varchar(3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [WeekOfMonth] varchar(1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [WeekOfQuarter] varchar(2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [WeekOfYear] varchar(2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [Month] varchar(2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [MonthName] varchar(9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [MonthOfQuarter] varchar(2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [Quarter] char(1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [QuarterName] varchar(9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [Year] char(4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [YearName] char(7) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [MonthYear] char(10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [MMYYYY] char(6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [FirstDayOfMonth] date NULL,
        [LastDayOfMonth] date NULL,
        [FirstDayOfQuarter] date NULL,
        [LastDayOfQuarter] date NULL,
        [FirstDayOfYear] date NULL,
        [LastDayOfYear] date NULL,
        [IsHolidayUSA] bit NULL,
        [IsWeekday] bit NULL,
        [HolidayUSA] varchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
    )
    WITH
    (
        DISTRIBUTION = ROUND_ROBIN,
        CLUSTERED COLUMNSTORE INDEX
    );
    
    CREATE TABLE [dbo].[Geography]
    (
        [GeographyID] int NOT NULL,
        [ZipCodeBKey] varchar(10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
        [County] varchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [City] varchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [State] varchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [Country] varchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [ZipCode] varchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
    )
    WITH
    (
        DISTRIBUTION = ROUND_ROBIN,
        CLUSTERED COLUMNSTORE INDEX
    );
    
    CREATE TABLE [dbo].[HackneyLicense]
    (
        [HackneyLicenseID] int NOT NULL,
        [HackneyLicenseBKey] varchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
        [HackneyLicenseCode] varchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
    )
    WITH
    (
        DISTRIBUTION = ROUND_ROBIN,
        CLUSTERED COLUMNSTORE INDEX
    );
    
    CREATE TABLE [dbo].[Medallion]
    (
        [MedallionID] int NOT NULL,
        [MedallionBKey] varchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
        [MedallionCode] varchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
    )
    WITH
    (
        DISTRIBUTION = ROUND_ROBIN,
        CLUSTERED COLUMNSTORE INDEX
    );
    
    CREATE TABLE [dbo].[Time]
    (
        [TimeID] int NOT NULL,
        [TimeBKey] varchar(8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
        [HourNumber] tinyint NOT NULL,
        [MinuteNumber] tinyint NOT NULL,
        [SecondNumber] tinyint NOT NULL,
        [TimeInSecond] int NOT NULL,
        [HourlyBucket] varchar(15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
        [DayTimeBucketGroupKey] int NOT NULL,
        [DayTimeBucket] varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
    )
    WITH
    (
        DISTRIBUTION = ROUND_ROBIN,
        CLUSTERED COLUMNSTORE INDEX
    );
    
    CREATE TABLE [dbo].[Trip]
    (
        [DateID] int NOT NULL,
        [MedallionID] int NOT NULL,
        [HackneyLicenseID] int NOT NULL,
        [PickupTimeID] int NOT NULL,
        [DropoffTimeID] int NOT NULL,
        [PickupGeographyID] int NULL,
        [DropoffGeographyID] int NULL,
        [PickupLatitude] float NULL,
        [PickupLongitude] float NULL,
        [PickupLatLong] varchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [DropoffLatitude] float NULL,
        [DropoffLongitude] float NULL,
        [DropoffLatLong] varchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [PassengerCount] int NULL,
        [TripDurationSeconds] int NULL,
        [TripDistanceMiles] float NULL,
        [PaymentType] varchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [FareAmount] money NULL,
        [SurchargeAmount] money NULL,
        [TaxAmount] money NULL,
        [TipAmount] money NULL,
        [TollsAmount] money NULL,
        [TotalAmount] money NULL
    )
    WITH
    (
        DISTRIBUTION = ROUND_ROBIN,
        CLUSTERED COLUMNSTORE INDEX
    );
    
    CREATE TABLE [dbo].[Weather]
    (
        [DateID] int NOT NULL,
        [GeographyID] int NOT NULL,
        [PrecipitationInches] float NOT NULL,
        [AvgTemperatureFahrenheit] float NOT NULL
    )
    WITH
    (
        DISTRIBUTION = ROUND_ROBIN,
        CLUSTERED COLUMNSTORE INDEX
    );
    

Carga de datos en el almacenamiento de datosLoad the data into your data warehouse

En esta sección se usa la instrucción COPY para cargar los datos de ejemplo desde Azure Storage Blob.This section uses the COPY statement to load the sample data from Azure Storage Blob.

Nota

En este tutorial se cargan los datos directamente en la tabla final.This tutorial loads the data directly into the final table. Normalmente, se cargan en una tabla de almacenamiento provisional para las cargas de trabajo de producción.You would typically load into a staging table for your production workloads. Con los datos en la tabla de almacenamiento provisional, puede realizar las transformaciones necesarias.While data is in the staging table you can perform any necessary transformations.

  1. Ejecute las siguientes instrucciones para cargar los datos:Run the following statements to load the data:

    COPY INTO [dbo].[Date]
    FROM 'https://nytaxiblob.blob.core.windows.net/2013/Date'
    WITH
    (
        FILE_TYPE = 'CSV',
        FIELDTERMINATOR = ',',
        FIELDQUOTE = ''
    )
    OPTION (LABEL = 'COPY : Load [dbo].[Date] - Taxi dataset');
    
    
    COPY INTO [dbo].[Geography]
    FROM 'https://nytaxiblob.blob.core.windows.net/2013/Geography'
    WITH
    (
        FILE_TYPE = 'CSV',
        FIELDTERMINATOR = ',',
        FIELDQUOTE = ''
    )
    OPTION (LABEL = 'COPY : Load [dbo].[Geography] - Taxi dataset');
    
    COPY INTO [dbo].[HackneyLicense]
    FROM 'https://nytaxiblob.blob.core.windows.net/2013/HackneyLicense'
    WITH
    (
        FILE_TYPE = 'CSV',
        FIELDTERMINATOR = ',',
        FIELDQUOTE = ''
    )
    OPTION (LABEL = 'COPY : Load [dbo].[HackneyLicense] - Taxi dataset');
    
    COPY INTO [dbo].[Medallion]
    FROM 'https://nytaxiblob.blob.core.windows.net/2013/Medallion'
    WITH
    (
        FILE_TYPE = 'CSV',
        FIELDTERMINATOR = ',',
        FIELDQUOTE = ''
    )
    OPTION (LABEL = 'COPY : Load [dbo].[Medallion] - Taxi dataset');
    
    COPY INTO [dbo].[Time]
    FROM 'https://nytaxiblob.blob.core.windows.net/2013/Time'
    WITH
    (
        FILE_TYPE = 'CSV',
        FIELDTERMINATOR = ',',
        FIELDQUOTE = ''
    )
    OPTION (LABEL = 'COPY : Load [dbo].[Time] - Taxi dataset');
    
    COPY INTO [dbo].[Weather]
    FROM 'https://nytaxiblob.blob.core.windows.net/2013/Weather'
    WITH
    (
        FILE_TYPE = 'CSV',
        FIELDTERMINATOR = ',',
        FIELDQUOTE = '',
        ROWTERMINATOR='0X0A'
    )
    OPTION (LABEL = 'COPY : Load [dbo].[Weather] - Taxi dataset');
    
    COPY INTO [dbo].[Trip]
    FROM 'https://nytaxiblob.blob.core.windows.net/2013/Trip2013'
    WITH
    (
        FILE_TYPE = 'CSV',
        FIELDTERMINATOR = '|',
        FIELDQUOTE = '',
        ROWTERMINATOR='0X0A',
        COMPRESSION = 'GZIP'
    )
    OPTION (LABEL = 'COPY : Load [dbo].[Trip] - Taxi dataset');
    
  2. Consulte los datos mientras se carga.View your data as it loads. Va a cargar varios gigabytes de datos y a comprimirlos en índices de almacén de columnas agrupados de alto rendimiento.You're loading several GBs of data and compressing it into highly performant clustered columnstore indexes. Ejecute la siguiente consulta, que usa vistas de administración dinámica (DMV) para mostrar el estado de la carga.Run the following query that uses a dynamic management views (DMVs) to show the status of the load.

    SELECT  r.[request_id]                           
    ,       r.[status]                               
    ,       r.resource_class                         
    ,       r.command
    ,       sum(bytes_processed) AS bytes_processed
    ,       sum(rows_processed) AS rows_processed
    FROM    sys.dm_pdw_exec_requests r
                  JOIN sys.dm_pdw_dms_workers w
                         ON r.[request_id] = w.request_id
    WHERE [label] = 'COPY : Load [dbo].[Date] - Taxi dataset' OR
        [label] = 'COPY : Load [dbo].[Geography] - Taxi dataset' OR
        [label] = 'COPY : Load [dbo].[HackneyLicense] - Taxi dataset' OR
        [label] = 'COPY : Load [dbo].[Medallion] - Taxi dataset' OR
        [label] = 'COPY : Load [dbo].[Time] - Taxi dataset' OR
        [label] = 'COPY : Load [dbo].[Weather] - Taxi dataset' OR
        [label] = 'COPY : Load [dbo].[Trip] - Taxi dataset' 
    and session_id <> session_id() and type = 'WRITER'
    GROUP BY r.[request_id]                           
    ,       r.[status]                               
    ,       r.resource_class                         
    ,       r.command;
    
  3. Consulte todas las consultas del sistema.View all system queries.

    SELECT * FROM sys.dm_pdw_exec_requests;
    
  4. Disfrute viendo cómo los datos se cargan ordenadamente en el almacenamiento de datos.Enjoy seeing your data nicely loaded into your data warehouse.

    Visualización de tablas cargadas

Limpieza de recursosClean up resources

Se le cobran recursos de proceso y datos que cargó en el almacenamiento de datos.You are being charged for compute resources and data that you loaded into your data warehouse. Estos se cobran por separado.These are billed separately.

  • Si desea conservar los datos de almacenamiento, puede pausar el proceso cuando no está utilizando el almacenamiento de datos.If you want to keep the data in storage, you can pause compute when you aren't using the data warehouse. Cuando pausa el proceso, solamente se cobra por el almacenamiento de datos y puede reanudar el proceso cada vez que está listo para trabajar con los datos.By pausing compute you will only be charge for data storage and you can resume the compute whenever you are ready to work with the data.
  • Si desea quitar cobros futuros, puede eliminar el almacenamiento de datos.If you want to remove future charges, you can delete the data warehouse.

Siga estos pasos para limpiar los recursos según estime oportuno.Follow these steps to clean up resources as you desire.

  1. Inicie la sesión en Azure Portal y seleccione el almacenamiento de datos.Log in to the Azure portal, select your data warehouse.

    Limpieza de recursos

  2. Para pausar el proceso, seleccione el botón Pausar.To pause compute, select the Pause button. Cuando el almacenamiento de datos se pause, verá un botón Iniciar.When the data warehouse is paused, you will see a Start button. Para reanudar el proceso, seleccione Iniciar.To resume compute, select Start.

  3. Para quitar el almacenamiento de datos para que no se le cobre por proceso o almacenamiento, seleccione Eliminar.To remove the data warehouse so you won't be charged for compute or storage, select Delete.

  4. Para eliminar el servidor que ha creado, seleccione mynewserver-20180430.database.windows.net en la imagen anterior y haga clic en Eliminar.To remove the server you created, select mynewserver-20180430.database.windows.net in the previous image, and then select Delete. Debe tener cuidado con esto, ya que la eliminación del servidor eliminará todas las bases de datos asignadas al servidor.Be careful with this as deleting the server will delete all databases assigned to the server.

  5. Para quitar el grupo de recursos, seleccione myResourceGroup y Eliminar grupo de recursos.To remove the resource group, select myResourceGroup, and then select Delete resource group.

Pasos siguientesNext steps

En este tutorial, aprendió a crear un almacenamiento de datos y a crear un usuario para cargar datos.In this tutorial, you learned how to create a data warehouse and create a user for loading data. Ha usado una instrucción COPY sencilla para cargar datos en el almacenamiento de datos.You used the simple COPY statement to load data into your data warehouse.

Hizo todo esto:You did these things:

  • Creó un almacenamiento de datos en Azure PortalCreated a data warehouse in the Azure portal
  • Establecer una regla de firewall de nivel de servidor en Azure PortalSet up a server-level firewall rule in the Azure portal
  • Se conectó al almacenamiento de datos con SSMSConnected to the data warehouse with SSMS
  • Creó un usuario designado para cargar datosCreated a user designated for loading data
  • Ha creado tablas para los datos de ejemplo.Created the tables for the sample data
  • Ha usado la instrucción COPY de T-SQL para cargar datos en el almacenamiento de datos.Used the COPY T-SQL statement to load data into your data warehouse
  • Vio el progreso de los datos a medida que se cargabanViewed the progress of data as it is loading

Avance a la introducción al desarrollo para obtener información sobre cómo migrar una base de datos existente a Azure Synapse Analytics:Advance to the development overview to learn how to migrate an existing database to Azure Synapse Analytics:

Para obtener más ejemplos y referencias de carga, consulte la siguiente documentación:For more loading examples and references, view the following documentation: