Menjalankan pekerjaan Apache Sqoop dengan Azure PowerShell di HDInsight
Pelajari cara menggunakan Azure PowerShell untuk menjalankan pekerjaan Apache Sqoop di Azure HDInsight untuk mengimpor dan mengekspor data antara kluster HDInsight dan Azure SQL Database atau SQL Server. Artikel ini adalah kelanjutan dari Menggunakan Apache Sqoop dengan Hadoop di HDInsight.
Prasyarat
Stasiun kerja dengan Modul AZ Azure PowerShell terpasang.
Penyelesaian Penyiapan lingkungan pengujian dari Menggunakan Apache Sqoop dengan Hadoop dalam HDInsight.
Keakraban dengan Sqoop. Untuk informasi selengkapnya, lihat Panduan Pengguna Sqoop.
Ekspor Sqoop
Dari Apache Hive ke SQL.
Contoh ini mengekspor data dari tabel hivesampletable
Apache Hive ke tabel mobiledata
di SQL. Atur nilai untuk variabel di bawah ini lalu jalankan perintah.
$hdinsightClusterName = ""
$httpPassword = ''
$sqlDatabasePassword = ''
# These values only need to be changed if the template was not followed.
$httpUserName = "admin"
$sqlServerLogin = "sqluser"
$sqlServerName = $hdinsightClusterName + "dbserver"
$sqlDatabaseName = $hdinsightClusterName + "db"
$pw = ConvertTo-SecureString -String $httpPassword -AsPlainText -Force
$httpCredential = New-Object System.Management.Automation.PSCredential($httpUserName,$pw)
# Connection string
$connectionString = "jdbc:sqlserver://$sqlServerName.database.windows.net;user=$sqlServerLogin@$sqlServerName;password=$sqlDatabasePassword;database=$sqlDatabaseName"
# start export
New-AzHDInsightSqoopJobDefinition `
-Command "export --connect $connectionString --table mobiledata --hcatalog-table hivesampletable" `
| Start-AzHDInsightJob `
-ClusterName $hdinsightClusterName `
-HttpCredential $httpCredential
Eksekusi alternatif
Kode di bawah ini melakukan ekspor yang sama; namun, ini menyediakan cara untuk membaca log output. Jalankan kode untuk memulai ekspor.
$sqoopCommand = "export --connect $connectionString --table mobiledata --hcatalog-table hivesampletable" $sqoopDef = New-AzHDInsightSqoopJobDefinition ` -Command $sqoopCommand $sqoopJob = Start-AzHDInsightJob ` -ClusterName $hdinsightClusterName ` -HttpCredential $httpCredential ` -JobDefinition $sqoopDef
Kode di bawah ini menampilkan log output. Jalankan kode di bawah ini:
Get-AzHDInsightJobOutput ` -ClusterName $hdinsightClusterName ` -HttpCredential $httpCredential ` -JobId $sqoopJob.JobId ` -DisplayOutputType StandardError Get-AzHDInsightJobOutput ` -ClusterName $hdinsightClusterName ` -HttpCredential $httpCredential ` -JobId $sqoopJob.JobId ` -DisplayOutputType StandardOutput
Jika Anda menerima pesan kesalahan, The specified blob does not exist.
, coba lagi setelah beberapa menit.
Impor Sqoop
Dari SQL ke Azure Storage. Contoh ini mengimpor data dari tabel mobiledata
dalam SQL, ke direktori wasb:///tutorials/usesqoop/importeddata
di HDInsight. Bidang dalam data dipisahkan oleh karakter tab, dan garis diakhiri oleh karakter baris baru. Contoh ini mengasumsikan Anda telah menyelesaikan contoh sebelumnya.
$sqoopCommand = "import --connect $connectionString --table mobiledata --target-dir wasb:///tutorials/usesqoop/importeddata --fields-terminated-by '\t' --lines-terminated-by '\n' -m 1"
$sqoopDef = New-AzHDInsightSqoopJobDefinition `
-Command $sqoopCommand
$sqoopJob = Start-AzHDInsightJob `
-ClusterName $hdinsightClusterName `
-HttpCredential $httpCredential `
-JobDefinition $sqoopDef
Get-AzHDInsightJobOutput `
-ClusterName $hdinsightClusterName `
-HttpCredential $httpCredential `
-JobId $sqoopJob.JobId `
-DisplayOutputType StandardError
Get-AzHDInsightJobOutput `
-ClusterName $hdinsightClusterName `
-HttpCredential $httpCredential `
-JobId $sqoopJob.JobId `
-DisplayOutputType StandardOutput
Contoh ekspor Sqoop tambahan
Ini adalah contoh kuat yang mengekspor data dari /tutorials/usesqoop/data/sample.log
dari akun penyimpanan default, lalu mengimpornya ke tabel yang disebut log4jlogs
dalam database SQL Server. Contoh ini tidak tergantung contoh sebelumnya.
Skrip PowerShell berikut memproses file sumber terlebih dahulu, lalu mengekspornya ke tabel log4jlogs
. Ganti CLUSTERNAME
, CLUSTERPASSWORD
, dan SQLPASSWORD
dengan nilai yang Anda gunakan dari prasyarat.
<#------ BEGIN USER INPUT ------#>
$hdinsightClusterName = "CLUSTERNAME"
$httpUserName = "admin" #default is admin, update as needed
$httpPassword = 'CLUSTERPASSWORD'
$sqlDatabasePassword = 'SQLPASSWORD'
<#------- END USER INPUT -------#>
# Other fixed variable that should be used as is
$sqlServerName = $hdinsightClusterName + "dbserver"
$sqlDatabaseName = $hdinsightClusterName + "db"
$tableName_log4j = "log4jlogs"
$exportDir_log4j = "/tutorials/usesqoop/data"
$sourceBlobName = "example/data/sample.log"
$destBlobName = "tutorials/usesqoop/data/sample.log"
$sqljdbcdriver = "/user/oozie/share/lib/sqoop/mssql-jdbc-7.0.0.jre8.jar"
$cluster = Get-AzHDInsightCluster -ClusterName $hdinsightClusterName
$defaultStorageAccountName = $cluster.DefaultStorageAccount -replace '.blob.core.windows.net'
$defaultStorageContainer = $cluster.DefaultStorageContainer
$resourceGroup = $cluster.ResourceGroup
$sqlServer = Get-AzSqlServer -ResourceGroupName $resourceGroup -ServerName $sqlServerName
$sqlServerLogin = $sqlServer.SqlAdministratorLogin
$sqlServerFQDN = $sqlServer.FullyQualifiedDomainName
#Connect to Azure subscription
Write-Host "`nConnecting to your Azure subscription ..." -ForegroundColor Green
try{Get-AzContext}
catch{Connect-AzAccount}
#pre-process the source file
Write-Host "`nPreprocessing the source file ..." -ForegroundColor Green
# This procedure creates a new file with $destBlobName
# Define the connection string
$defaultStorageAccountKey = (Get-AzStorageAccountKey `
-ResourceGroupName $resourceGroup `
-Name $defaultStorageAccountName)[0].Value
# Create block blob objects referencing the source and destination blob.
$storageAccount = Get-AzStorageAccount `
-ResourceGroupName $resourceGroup `
-Name $defaultStorageAccountName
$storageContainer = ($storageAccount |Get-AzStorageContainer -Name $defaultStorageContainer).CloudBlobContainer
$sourceBlob = $storageContainer.GetBlockBlobReference($sourceBlobName)
$destBlob = $storageContainer.GetBlockBlobReference($destBlobName)
# Define a MemoryStream and a StreamReader for reading from the source file
$stream = New-Object System.IO.MemoryStream
$stream = $sourceBlob.OpenRead()
$sReader = New-Object System.IO.StreamReader($stream)
# Define a MemoryStream and a StreamWriter for writing into the destination file
$memStream = New-Object System.IO.MemoryStream
$writeStream = New-Object System.IO.StreamWriter $memStream
# Pre-process the source blob
$exString = "java.lang.Exception:"
while(-Not $sReader.EndOfStream){
$line = $sReader.ReadLine()
$split = $line.Split(" ")
# remove the "java.lang.Exception" from the first element of the array
# for example: java.lang.Exception: 2012-02-03 19:11:02 SampleClass8 [WARN] problem finding id 153454612
if ($split[0] -eq $exString){
#create a new ArrayList to remove $split[0]
$newArray = [System.Collections.ArrayList] $split
$newArray.Remove($exString)
# update $split and $line
$split = $newArray
$line = $newArray -join(" ")
}
# remove the lines that has less than 7 elements
if ($split.count -ge 7){
write-host $line
$writeStream.WriteLine($line)
}
}
# Write to the destination blob
$writeStream.Flush()
$memStream.Seek(0, "Begin")
$destBlob.UploadFromStream($memStream)
#export the log file from the cluster to SQL
Write-Host "Exporting the log file ..." -ForegroundColor Green
$pw = ConvertTo-SecureString -String $httpPassword -AsPlainText -Force
$httpCredential = New-Object System.Management.Automation.PSCredential($httpUserName,$pw)
# Connection string
$connectionString = "jdbc:sqlserver://$sqlServerFQDN;user=$sqlServerLogin@$sqlServerName;password=$sqlDatabasePassword;database=$sqlDatabaseName"
# Submit a Sqoop job
$sqoopDef = New-AzHDInsightSqoopJobDefinition `
-Command "export --connect $connectionString --table $tableName_log4j --export-dir $exportDir_log4j --input-fields-terminated-by \0x20 -m 1" `
-Files $sqljdbcdriver
$sqoopJob = Start-AzHDInsightJob `
-ClusterName $hdinsightClusterName `
-HttpCredential $httpCredential `
-JobDefinition $sqoopDef
Wait-AzHDInsightJob `
-ResourceGroupName $resourceGroup `
-ClusterName $hdinsightClusterName `
-HttpCredential $httpCredential `
-JobId $sqoopJob.JobId
Write-Host "Standard Error" -BackgroundColor Green
Get-AzHDInsightJobOutput `
-ResourceGroupName $resourceGroup `
-ClusterName $hdinsightClusterName `
-DefaultStorageAccountName $defaultStorageAccountName `
-DefaultStorageAccountKey $defaultStorageAccountKey `
-DefaultContainer $defaultStorageContainer `
-HttpCredential $httpCredential `
-JobId $sqoopJob.JobId `
-DisplayOutputType StandardError
Write-Host "Standard Output" -BackgroundColor Green
Get-AzHDInsightJobOutput `
-ResourceGroupName $resourceGroupName `
-ClusterName $hdinsightClusterName `
-DefaultStorageAccountName $defaultStorageAccountName `
-DefaultStorageAccountKey $defaultStorageAccountKey `
-DefaultContainer $defaultStorageContainer `
-HttpCredential $httpCredential `
-JobId $sqoopJob.JobId `
-DisplayOutputType StandardOutput
Batasan
HDInsight berbasis Linux menyajikan batasan berikut:
Ekspor massal: Konektor Sqoop yang digunakan untuk mengekspor data ke SQL saat ini tidak mendukung sisipan massal.
Batching: Dengan menggunakan sakelar
-batch
saat melakukan penyisipan, Sqoop melakukan banyak penyisipan alih-alih mengelompokkan operasi penyisipan.
Langkah berikutnya
Sekarang Anda telah mempelajari cara menggunakan Sqoop. Untuk mempelajari selengkapnya, lihat:
- Menggunakan Apache Oozie dengan HDInsight: Gunakan tindakan Sqoop dalam alur kerja Oozie.
- Mengunggah data ke HDInsight: Temukan metode lain untuk mengunggah data ke penyimpanan HDInsight atau Azure Blob.