Skapa tabeller, massimportera och fråga efter data

  • 10 minuter

Universitetet måste ha en plats där de kan lagrar de data som just nu finns i textfiler. De vill göra datan relationsbaserad för att förbättra möjligheten att få åtkomst till den. De har valt en enkel databas i Azure SQL Database som lagringstjänst för dessa data. Låt oss ta en titt på SQL Database och se hur man laddar upp och frågar efter data.

Skapa en enkel databas med hjälp av Azure-portalen

SQL Database är en relationsdatabastjänst som baseras på den senaste stabila versionen av Microsoft SQL Server Database Engine. SQL Database är en lättanvänd databas med hög prestanda som är tillförlitlig och mycket säker. Du kan använda SQL Database för att skapa nya appar, webbplatser och mikrotjänster på valfritt programmeringsspråk och du behöver inte hantera infrastrukturen.

Du kan skapa en enkel databas via Azure-portalen eller genom att använda Azure PowerShell eller CLI.

  1. Från Azure-portal menyn, välj skapa en resurs.

    Screenshot of Azure portal menu and Create a resource option.

  2. Välj Databaser och sedan SQL Database.

    Screenshot of the Databases and SQL Database options.

  3. Kör kommandona az sql server create och az sql db create för att använda CLI.

  4. Kör kommandona New-AzSqlServer och New-AzSqlDatabase för att använda PowerShell.

När du skapar en enkel databas, uppmanas du att ange vilken server som ska hantera den. Du kan skapa en ny server eller använda en som redan finns.

När du skapar en ny server och väljer att använda SQL-autentisering uppmanas du att ange ett användarnamn och lösenord för serveradministratören. Använd dessa autentiseringsuppgifter för att ansluta till servern för att utföra administrativa uppgifter och för att få åtkomst till de databaser som servern styr. SQL Database stöder även Microsoft Entra-autentisering. Du kan också välja att använda både SQL- och Microsoft Entra-autentisering. Men du måste alltid ange en administratör eller skapa ett administratörskonto när du skapar en ny server. Bevilja sedan åtkomst till konton som lagras i Microsoft Entra-ID.

Varje databasserver skyddas av en brandvägg som blockerar potentiellt skadliga processer. Du kan öppna brandväggen för andra Azure-tjänster. Och du kan selektivt aktivera åtkomst till andra datorer baserat på deras IP-adress eller adressintervall. SQL Database tillhandahåller även avancerad datasäkerhet som gör att du kan:

  • Ange känsligheten för data i enskilda kolumner i tabeller.
  • Utvärdera sårbarheten för dina databaser och vidta nödvändiga åtgärder för reparation.
  • Skicka aviseringar när ett hot har identifierats.

Du konfigurerar resurser med hjälp av modellen för virtuell kärna (vCore), som anger vilka minnes-, I/O- och CPU-resurser som ska allokeras. Du kan skala beräknings- och lagringsresurserna oberoende av varandra. Du kan också tilldela resurser för databastransaktionsenheter (DTU:er). En DTU är ett mått på kalibrerad kostnad för de resurser som krävs för att utföra en testad transaktion.

Om du har flera databaser och resurskraven för dessa databaser varierar, kan du använda en elastisk SQL-pool. Den här funktionen gör det möjligt att dela en resurspool mellan pooldatabaser vid behov.

När du skapar en databas anger du också hur data sorteras. En sortering definierar de regler som databasen använder för att sortera och jämföra data. Den anger också vilken teckenuppsättning som ska användas för textdata. Du kan ändra sorteringen när du har skapat databasen, men det rekommenderas inte om databasen innehåller data.

Skapa tabeller

Du kan använda något av dessa verktyg för att skapa tabeller:

  • Frågeredigeraren i Azure-portalen
  • sqlcmd-verktyget och Cloud Shell
  • SQL Server Management Studio

Oavsett vilket verktyg du väljer definierar du tabellen med hjälp CREATE TABLE av kommandot Transact-SQL (T-SQL). SQL Database stöder primärnycklar, sekundärnycklar, index och utlösare i tabeller. Följande exempelkod skapar ett par relaterade tabeller och ett icke-grupperat index. Du kan köra dessa kommandon som en batch i frågeredigeraren eller i sqlcmd-verktyget.

CREATE TABLE MyTable
(
    MyColumn1 INT NOT NULL PRIMARY KEY,
    MyColumn2 VARCHAR(50) NOT NULL
);

CREATE TABLE MyTable2
(
    AnotherColumn1 INT NOT NULL,
    AnotherColumn2 INT NOT NULL REFERENCES MyTable,
    AnotherColumn3 VARCHAR(50) NULL,
    PRIMARY KEY (AnotherColumn1, AnotherColumn2)
);

CREATE INDEX cci ON MyTable2(AnotherColumn3);

När du vill öppna frågeredigeraren i Azure-portalen, går du till sidan för din databas och väljer Frågeredigeraren. Du uppmanas att ange autentiseringsuppgifter. Du kan ställa in Auktoriseringstyp till SQL Server-autentisering och ange det användarnamn och lösenord som du konfigurerade när du skapade databasen. Eller så kan du välja Active Directory-lösenordsautentisering och ange autentiseringsuppgifterna för en behörig användare i Microsoft Entra-ID. Om enkel inloggning i Active Directory Domain Services är aktiverat, kan du ansluta med din Azure-identitet.

The SQL Database sign-in page in the Azure portal.

Du anger din T-SQL-kod i frågefönstret och väljer sedan Kör för att köra den. Om T-SQL-instruktionen är en fråga visas alla rader som returneras i fönstret Resultat . Fönstret Meddelande visar information om till exempel antalet rader som returnerades eller eventuella fel som inträffade:

The query editor in the Azure portal with the various panes highlighted.

Om du vill använda sqlcmd-verktyget går du till Cloud Shell och kör nedanstående kommando. Ersätt <server> med namnet på den databasserver som du skapade, <database> med namnet på databasen, samt <user name> och <password> med dina autentiseringsuppgifter.

sqlcmd -S <server>.database.windows.net -d <database> -U <username> -P <password>

Om inloggningskommandot lyckas visas en 1> uppmaning. Du kan ange T-SQL-kommandon på flera rader och sedan skriva GO för att köra dem.

Massimportera data med BCP

Microsoft erbjuder flera verktyg som du kan använda för att ladda upp data till din SQL Database:

  • SQL Server Integration Services (SSIS)
  • BULK INSERT-instruktion i SQL
  • Verktyget Bulk Copy Program (BCP)

bcp-verktyget används ofta eftersom det är praktiskt och enkelt att skriptbasera när du importerar data till flera tabeller. bcp-verktyget är ett kommandoradsverktyg som du kan använda när du importerar och exporterar data från en databas. Om du ska importera data kräver bcp följande tre saker:

  • Källdata som ska laddas upp.
  • En befintlig tabell i måldatabasen.
  • En formatfil som definierar dataformatet och hur du mappar data till kolumner i måltabellen.

bcp-verktyget är flexibelt. Källdatan kan finnas i nästan alla strukturerade format. Formatfilen anger datalayouten och om data är binära eller teckenbaserade. Den anger också typ och längd för varje objekt och hur data separeras. Formatfilen anger också hur varje objekt ska mappas i filen till en kolumn i tabellen. Det är viktigt att definiera innehållet i den här filen korrekt. Annars kanske dina data inte importeras, eller så kan datan läsas in i fel kolumner.

Anta att du har följande data i filen mydata.csv och du vill importera dessa data till tabellen MyTable som vi skapade tidigare.

Column1,Column2
99,some text
101,some more text
97,another bit of text
87,yet more text
33,a final bit of text

Den första raden innehåller fältnamn som inte är samma som i kolumnerna i tabellen. Data är kommaavgränsade och varje rad avslutas med ett nytt radtecken. Kom ihåg att ordningen på kolumnerna i filen kan skilja sig från tabellen. I det här exemplet är den första kolumnen i tabellen numerisk och den andra kolumnen är en sträng, enligt följande:

CREATE TABLE MyTable
(
    MyColumn1 INT NOT NULL PRIMARY KEY,
    MyColumn2 VARCHAR(50) NOT NULL
);

Du kan använda bcp-kommandot till att skapa en formatfil för import. bcp-kommandot kan skapa en formatfil som baseras på schemat för måltabellen i databasen. Därefter kan du redigera filen så att den matchar datan i källfilen.

Kör följande kommando för att skapa en formatfil. Ersätt objekten i vinkelparenteserna med värden för din databas, server, användarnamn och lösenord:

bcp <database>.dbo.mytable format nul -c -f mytable.fmt -t, -S <server>.database.windows.net -U <username> -P <password>

bcp-verktyget har flera parametrar som styr funktionaliteten i verktyget. Du kan ange:

  • Måltabellen (<database>.<schema>.<table>)
  • De data som ska importeras och information om datan (format nul -c -f mytable.fmt -t,)
  • Anslutningsinformation för din databas (-S <server>.database.windows.net -U <username> -P <password>)

Fullständig syntax och kommandoradsparametrar för verktyget finns i hjälpdokumentationen.

Kommandot genererar filen mytable.fmt-format med innehåll som ser ut så här:

14.0
2
1       SQLCHAR             0       12      ","    1     MyColumn1                                ""
2       SQLCHAR             0       50      "\n"   2     MyColumn2                                SQL_Latin1_General_CP1_CI_AS

Den första raden visar det interna versionsnumret för SQL Database. Den andra raden visar antalet kolumner i källtabellen. De sista två raderna visar hur data kommer att mappas i källfilen till dessa kolumner.

Båda raderna börjar med ett tal som är kolumnnumret i tabellen. Det andra fältet (SQLCHAR) anger att när vi använder den här formatfilen till att importera data, innehåller varje fält i källfilen teckendata. Verktyget bcp försöker konvertera dessa data till lämplig typ för motsvarande kolumn i tabellen. Nästa fält (12 och 50) är längden på datan i varje kolumn i databasen. Ändra inte det här fältet! Följande objekt (”,” och ”\n”) är fältavgränsaren i källfilen respektive tecknet för ny rad. Nästa kolumn är fältnumret från källfilen. Det näst sista fältet (MyColumn1 och MyColumn2) är namnet på kolumnen i databasen. Det sista fältet är den sortering som ska användas, vilket endast gäller teckendata i databasen.

Kom ihåg att fälten i källfilen har en annan ordning än kolumnerna i databasen. Du bör därför redigera formatfilen och ändra fältnumren enligt nedan:

14.0
2
1       SQLCHAR             0       12      ","    2     MyColumn1                                ""
2       SQLCHAR             0       50      "\n"   1     MyColumn2                                SQL_Latin1_General_CP1_CI_AS

Data i fält 2 i källfilen mappas till den första kolumnen i databasen. Fält 1 mappas till den andra kolumnen.

Du kan nu använda bcp-kommandot till att importera data, enligt följande:

bcp <database>.dbo.mytable in mydata.csv -f mytable.fmt -S <server>.database.windows.net -U <username> -P <password> -F 2

in-flaggan visar att vi använder bcp till att importera data. Du kan använda out för att överföra data från en databas till en fil. -F 2-flaggan visar att importen ska starta på rad 2 i källfilen. Kom ihåg att den första raden innehåller huvuden i stället för data.

Kommandot körs och returnerar meddelanden som liknar dessa exempel:

Starting copy...

5 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total     : 46     Average : (108.7 rows per sec.)

Den viktiga raden i utdata är "5 rader kopierade". Antalet rader i källfilen som innehåller data som importerades. Om det någon annan siffra (eller noll) kan din formatfil vara felaktig.

Fråga efter data

Kontrollera att importen lyckades genom att köra frågor mot datan. Du kan använda frågeredigeraren från Azure-portalen. Du kan också använda sqlcmd-verktyget för att ansluta till databasen från en kommandorad. I båda fallen kan du köra en SELECT-instruktion som denna:

SELECT *
FROM MyTable;

Du bör se nedanstående resultat.

The query editor in the Azure portal shows the results of a query.