Welcome to the third article in our Azure Data Lake Analytics series! In this article, we will dive into the concepts of deploying a database, creating schemas, and defining tables using U-SQL scripts.

Introduction

Azure Data Lake Analytics (ADLA) is a powerful analytics service provided by Microsoft. It allows you to query and analyze large amounts of data stored in various data sources without the need to copy the data into a single location. U-SQL, the language used in ADLA, is similar to T-SQL and combines the power of C# language.

In this article, we will explore how to create a database, define schemas, and create tables using U-SQL scripts.

Creating a User-Defined Database

In SQL Server, we use the CREATE DATABASE statement to create a new database. Similarly, in U-SQL, we use the same CREATE DATABASE statement to create a new database. Here’s an example:

CREATE DATABASE IF NOT EXISTS SQLShackDemo;

This script creates a database named “SQLShackDemo” if it does not already exist. You can submit this script to your ADLA account to create the database.

Defining Database Schema

A database schema is a collection of similar objects within a database. In SQL Server, we have system-defined and user-defined schemas. Similarly, U-SQL also has a default schema called “dbo” in every database. Let’s add a new user-defined schema called “Sales” in our “SQLShackDemo” database:

USE SQLShackDemo;
CREATE SCHEMA IF NOT EXISTS Sales;

This script creates a new schema named “Sales” in the “SQLShackDemo” database.

Creating Tables in ADLA

In ADLA, we can create tables similar to SQL Server tables. There are two types of tables: managed tables and external tables.

Managed tables have both table definition and table data, and U-SQL maintains data consistency between the table schema and data. On the other hand, external tables define only the table schema, and the reference data exists externally. Data can exist in Azure SQL Database, SQL on Azure VM, or other external sources.

Let’s create a managed table in ADLA using the following script:

USE SQLShackDemo;
USE Sales;

CREATE TABLE IF NOT EXISTS SalesRecords (
    [Region] string,
    [Country] string,
    [ItemType] string,
    [SalesChannel] string,
    [OrderPriority] string,
    [OrderDate] DateTime,
    [OrderID] int,
    [ShipDate] DateTime,
    [UnitsSold] int,
    [UnitPrice] decimal,
    [UnitCost] decimal,
    [TotalRevenue] decimal,
    [TotalCost] decimal,
    [TotalProfit] decimal
);

This script creates a managed table named “SalesRecords” in the “Sales” schema of the “SQLShackDemo” database. The table has various columns with their respective data types.

Conclusion

In this article, we explored the concepts of creating a database, defining schemas, and creating tables in Azure Data Lake Analytics using U-SQL scripts. We learned how to create a user-defined database, define a schema, and create a managed table. Stay tuned for the next article in this series!