Aracılığıyla paylaş

R ve SQL Server kullanarak veri özellikleri oluşturma (izlenecek yol)

Şunlar için geçerlidir: SQL Server 2016 (13.x) ve sonraki sürümleri

Veri mühendisliği, makine öğrenmesinin önemli bir parçasıdır. Verileri tahmine dayalı modelleme için kullanabilmeniz için genellikle dönüştürme gerekir. Veriler ihtiyacınız olan özelliklere sahip değilse bunları mevcut değerlerden oluşturabilirsiniz.

Bu modelleme görevi için, teslim alma ve bırakma konumunun ham enlem ve boylam değerlerini kullanmak yerine, iki konum arasındaki mesafenin mil cinsinden olmasını istersiniz. Bu özelliği oluşturmak için,haversine formülünü kullanarak iki nokta arasındaki doğrudan doğrusal uzaklığı hesaplarsınız.

Bu adımda, verilerden özellik oluşturmak için iki farklı yöntem öğrenin:

  • Özel R işlevi kullanma
  • Transact-SQL'da özel T-SQL işlevi kullanma

Amaç, özgün sütunları ve direct_distanceyeni sayısal özelliği içeren yeni bir SQL Server veri kümesi oluşturmaktır.

Önkoşullar

Bu adım, bu kılavuzdaki önceki adımlara göre devam eden bir R oturumu olduğunu varsayar. Bu adımlarda oluşturulan bağlantı dizelerini ve veri kaynağı nesnelerini kullanır. Script'i çalıştırmak için aşağıdaki araçlar ve paketler kullanılır.

  • R komutlarını çalıştırmak için Rgui.exe
  • T-SQL çalıştırmak için Management Studio

R kullanarak özellik kazandırma

R dili zengin ve çeşitli istatistiksel kitaplıklarıyla iyi bilinir, ancak yine de özel veri dönüştürmeleri oluşturmanız gerekebilir.

İlk olarak, bunu R kullanıcılarının alışkın olduğu şekilde yapalım: Verileri dizüstü bilgisayarınıza alın ve ardından enlem ve boylam değerleriyle belirtilen iki nokta arasındaki doğrusal uzaklığı hesaplayan computeDistözel bir R işlevi çalıştıralım.

  1. Daha önce oluşturduğunuz veri kaynağı nesnesinin yalnızca ilk 1000 satırı aldığını unutmayın. Şimdi tüm verileri alan bir sorgu tanımlayalım.

    bigQuery <- "SELECT tipped, fare_amount, passenger_count,trip_time_in_secs,trip_distance, pickup_datetime, dropoff_datetime,  pickup_latitude, pickup_longitude,  dropoff_latitude, dropoff_longitude FROM nyctaxi_sample";

  2. Sorguyu kullanarak yeni bir veri kaynağı nesnesi oluşturun.

    featureDataSource <- RxSqlServerData(sqlQuery = bigQuery,colClasses = c(pickup_longitude = "numeric", pickup_latitude = "numeric", dropoff_longitude = "numeric", dropoff_latitude = "numeric", passenger_count  = "numeric", trip_distance  = "numeric", trip_time_in_secs  = "numeric", direct_distance  = "numeric"), connectionString = connStr);

    • RxSqlServerData, sqlQuery parametresinin bağımsız değişkeni olarak sağlanan geçerli bir SELECT sorgusundan oluşan bir sorguyu veya tablo parametresi olarak sağlanan tablo nesnesinin adını alabilir.

    • Tablodan veri örneklemek istiyorsanız sqlQuery parametresini kullanmanız, T-SQL TABLESAMPLE yan tümcesini kullanarak örnekleme parametrelerini tanımlamanız ve rowBuffering bağımsız değişkenini YANLIŞ olarak ayarlamanız gerekir.

  3. Özel R işlevini oluşturmak için aşağıdaki kodu çalıştırın. İşlemci enlem ve boylam değerlerinin iki çiftini alır ve aralarındaki doğrusal uzaklığı hesaplayarak uzaklığı mil cinsinden döndürür.

    env <- new.env();
env$ComputeDist <- function(pickup_long, pickup_lat, dropoff_long, dropoff_lat){
  R <- 6371/1.609344 #radius in mile
  delta_lat <- dropoff_lat - pickup_lat
  delta_long <- dropoff_long - pickup_long
  degrees_to_radians = pi/180.0
  a1 <- sin(delta_lat/2*degrees_to_radians)
  a2 <- as.numeric(a1)^2
  a3 <- cos(pickup_lat*degrees_to_radians)
  a4 <- cos(dropoff_lat*degrees_to_radians)
  a5 <- sin(delta_long/2*degrees_to_radians)
  a6 <- as.numeric(a5)^2
  a <- a2+a3*a4*a6
  c <- 2*atan2(sqrt(a),sqrt(1-a))
  d <- R*c
  return (d)
}
    • İlk satır yeni bir ortam tanımlar. R'de bir ortam, paketlerdeki ad alanlarını kapsüllemek için kullanılabilir. çalışma alanınızdaki ortamları görüntülemek için search() işlevini kullanabilirsiniz. Belirli bir ortamdaki nesneleri görüntülemek için ls(<envname>)yazın.
    • $env.ComputeDist ile başlayan satırlar, küredeki iki nokta arasındaki büyük daire mesafesini hesaplayan haversine formülünü tanımlayan kodu içerir.

  4. İşlevi tanımladıktan sonra, direct_distanceyeni bir özellik sütunu oluşturmak için verilere uygularsınız. ancak dönüştürmeyi çalıştırmadan önce işlem bağlamını yerel olarak değiştirin.

    rxSetComputeContext("local");

  5. Özellik mühendisliği verilerini almak için rxDataStep işlevini çağırın ve env$ComputeDist işlevini bellekteki verilere uygulayın.

    start.time <- proc.time();

changed_ds <- rxDataStep(inData = featureDataSource,
transforms = list(direct_distance=ComputeDist(pickup_longitude,pickup_latitude, dropoff_longitude, dropoff_latitude),
tipped = "tipped", fare_amount = "fare_amount", passenger_count = "passenger_count",
trip_time_in_secs = "trip_time_in_secs",  trip_distance="trip_distance",
pickup_datetime = "pickup_datetime",  dropoff_datetime = "dropoff_datetime"),
transformEnvir = env,
rowsPerRead=500,
reportProgress = 3);

used.time <- proc.time() - start.time;
print(paste("It takes CPU Time=", round(used.time[1]+used.time[2],2)," seconds, Elapsed Time=", round(used.time[3],2), " seconds to generate features.", sep=""));
    • rxDataStep işlevi, verileri yerinde değiştirmek için çeşitli yöntemleri destekler. Daha fazla bilgi için şu makaleye bakın: Microsoft R'de verileri dönüştürme ve alt küme oluşturma

    Ancak rxDataStep ile ilgili dikkate değer birkaç nokta:

    Diğer veri kaynaklarında varsToKeep ve varsToDropbağımsız değişkenlerini kullanabilirsiniz, ancak bunlar SQL Server veri kaynakları için desteklenmez. Bu nedenle, bu örnekte hem geçiş sütunlarını hem de dönüştürülen sütunları belirtmek için dönüştürme bağımsız değişkenini kullandık. Ayrıca, SQL Server işlem bağlamında çalışırken, inData bağımsız değişkeni yalnızca bir SQL Server veri kaynağı kabul edebilir.

    Yukarıdaki kod, daha büyük veri kümelerinde çalıştırıldığında bir uyarı iletisi de oluşturabilir. Oluşturulan satır sayısı ile sütun sayısının çarpımı belirlenmiş bir değeri aştığında (varsayılan değer 3.000.000'dür), rxDataStep bir uyarı döndürür ve döndürülen veri çerçevesindeki satır sayısı kesilir. Uyarıyı kaldırmak için, rxDataStep işlevindeki maxRowsByCols bağımsız değişkenini değiştirebilirsiniz. Ancak maxRowsByCols çok büyükse, veri çerçevesini belleğe yüklerken sorunlarla karşılaşabilirsiniz.

  6. İsteğe bağlı olarak, dönüştürülen veri kaynağının şemasını incelemek için rxGetVarInfo çağırabilirsiniz.

    rxGetVarInfo(data = changed_ds);

Transact-SQL kullanarak özellik kazandırma

Bu alıştırmada, özel R işlevleri yerine SQL işlevlerini kullanarak aynı görevi gerçekleştirmeyi öğrenin.

T-SQL betiğini çalıştırmak için SQL Server Management Studio'ya (SSMS) veya başka bir sorgu düzenleyicisine geçin.

  1. fnCalculateDistanceadlı bir SQL işlevi kullanın. İşlev NYCTaxi_Sample veritabanında zaten mevcut olmalıdır. Nesne Gezgini'nde şu yola giderek fonksiyonun var olduğunu doğrulayın: Veritabanları > NYCTaxi_Sample > Programlanabilirlik > Fonksiyonlar > Skalar Değerli Fonksiyonlar > dbo.fnCalculateDistance.

    İşlev yoksa, NYCTaxi_Sample veritabanında işlevi oluşturmak için SQL Server Management Studio'yu kullanın.

    CREATE FUNCTION [dbo].[fnCalculateDistance] (@Lat1 float, @Long1 float, @Lat2 float, @Long2 float)
-- User-defined function calculates the direct distance between two geographical coordinates.
RETURNS decimal(28, 10)
AS
BEGIN
  DECLARE @distance decimal(28, 10)
  -- Convert to radians
  SET @Lat1 = @Lat1 / 57.2958
  SET @Long1 = @Long1 / 57.2958
  SET @Lat2 = @Lat2 / 57.2958
  SET @Long2 = @Long2 / 57.2958
  -- Calculate distance
  SET @distance = (SIN(@Lat1) * SIN(@Lat2)) + (COS(@Lat1) * COS(@Lat2) * COS(@Long2 - @Long1))
  --Convert to miles
  IF @distance <> 0
  BEGIN
    SET @distance = 3958.75 * ATAN(SQRT(1 - POWER(@distance, 2)) / @distance);
  END
  RETURN @distance
END

  2. Management Studio'da, yeni bir sorgu penceresinde, işlevin nasıl çalıştığını görmek için Transact-SQL destekleyen herhangi bir uygulamadan aşağıdaki Transact-SQL deyimini çalıştırın.

    USE nyctaxi_sample
GO

SELECT tipped, fare_amount, passenger_count,trip_time_in_secs,trip_distance, pickup_datetime, dropoff_datetime,
dbo.fnCalculateDistance(pickup_latitude, pickup_longitude, dropoff_latitude, dropoff_longitude) as direct_distance, pickup_latitude, pickup_longitude,  dropoff_latitude, dropoff_longitude 
FROM nyctaxi_sample

  3. Değerleri doğrudan yeni bir tabloya eklemek için (önce bunu oluşturmanız gerekir), tablo adını belirten bir INTO yan tümcesi ekleyebilirsiniz.

    USE nyctaxi_sample
GO

SELECT tipped, fare_amount, passenger_count, trip_time_in_secs, trip_distance, pickup_datetime, dropoff_datetime,
dbo.fnCalculateDistance(pickup_latitude, pickup_longitude, dropoff_latitude, dropoff_longitude) as direct_distance, pickup_latitude, pickup_longitude, dropoff_latitude, dropoff_longitude
INTO NewFeatureTable
FROM nyctaxi_sample

  4. SQL işlevini R kodundan da çağırabilirsiniz. Rgui'ye geri dönün ve SQL özellik oluşturma sorgusunu bir R değişkeninde depolayın.

    featureEngineeringQuery = "SELECT tipped, fare_amount, passenger_count,
    trip_time_in_secs,trip_distance, pickup_datetime, dropoff_datetime,
    dbo.fnCalculateDistance(pickup_latitude, pickup_longitude,  dropoff_latitude, dropoff_longitude) as direct_distance,
    pickup_latitude, pickup_longitude,  dropoff_latitude, dropoff_longitude
    FROM nyctaxi_sample
    tablesample (1 percent) repeatable (98052)"

    Bahşiş

    Bu sorgu, daha küçük bir veri örneği almak ve bu kılavuzu daha hızlı hale getirmek için yapıldı. Tüm verileri almak istiyorsanız TABLESAMPLE yan tümcesini kaldırabilirsiniz; ancak ortamınıza bağlı olarak, tam veri kümesini R'ye yüklemek mümkün olmayabilir ve hataya neden olabilir.

  5. R ortamınızdan Transact-SQL işlevini çağırmak ve featureEngineeringQueryiçinde tanımlanan verilere uygulamak için aşağıdaki kod satırlarını kullanın.

    featureDataSource = RxSqlServerData(sqlQuery = featureEngineeringQuery,
  colClasses = c(pickup_longitude = "numeric", pickup_latitude = "numeric",
    dropoff_longitude = "numeric", dropoff_latitude = "numeric",
    passenger_count  = "numeric", trip_distance  = "numeric",
    trip_time_in_secs  = "numeric", direct_distance  = "numeric"),
  connectionString = connStr)

  6. Yeni özellik oluşturulduğuna göre, özellik tablosundaki verilerin özetini oluşturmak için rxGetVarsInfo çağrısı yapın.

    rxGetVarInfo(data = featureDataSource)

    Sonuçları

    Var 1: tipped, Type: integer
Var 2: fare_amount, Type: numeric
Var 3: passenger_count, Type: numeric
Var 4: trip_time_in_secs, Type: numeric
Var 5: trip_distance, Type: numeric
Var 6: pickup_datetime, Type: character
Var 7: dropoff_datetime, Type: character
Var 8: direct_distance, Type: numeric
Var 9: pickup_latitude, Type: numeric
Var 10: pickup_longitude, Type: numeric
Var 11: dropoff_latitude, Type: numeric
Var 12: dropoff_longitude, Type: numeric

    Not

    Bazı durumlarda şuna benzer bir hata alabilirsiniz: 'fnCalculateDistance' nesnesinde EXECUTE izni reddedildi Bu durumda, kullandığınız oturum açma bilgilerinin yalnızca örnekte değil betik çalıştırma ve veritabanında nesne oluşturma izinlerine sahip olduğundan emin olun. fnCalculateDistance nesnesinin şemasını denetleyin. Nesne veritabanı sahibi tarafından oluşturulduysa ve oturum açma bilgileriniz rol db_datareader aitse, betiği çalıştırmak için oturum açma açık izinleri vermeniz gerekir.

R işlevleriyle SQL işlevlerini karşılaştırma

R kodunu zaman için kullanılan bu kod parçasını hatırlıyor musunuz?

start.time <- proc.time()
<your code here>
used.time <- proc.time() - start.time
print(paste("It takes CPU Time=", round(used.time[1]+used.time[2],2)," seconds, Elapsed Time=", round(used.time[3],2), " seconds to generate features.", sep=""))

Sql işlevini çağırırken veri dönüştürme işleminin ne kadar sürdüğünü görmek için bunu SQL özel işlev örneğiyle kullanmayı deneyebilirsiniz. Ayrıca rxSetComputeContext ile işlem bağlamlarını değiştirmeyi deneyin ve zamanlamaları karşılaştırın.

Zamanlarınız, ağ hızınıza ve donanım yapılandırmanıza bağlı olarak önemli ölçüde farklılık gösterebilir. Test ettiğimiz yapılandırmalarda, Transact-SQL işlevi yaklaşımı özel R işlevi kullanmaktan daha hızlıydı. Bu nedenle, sonraki adımlarda bu hesaplamalar için Transact-SQL işlevini kullandık.

Bahşiş

Çoğu zaman, Transact-SQL kullanan özellik mühendisliği R'den daha hızlı olacaktır. Örneğin, T-SQL hareketli ortalamalar ve n-kutucuklar gibi yaygın veri bilimi hesaplamalarına uygulanabilen hızlı pencereleme ve derecelendirme işlevleri içerir. Verilerinize ve göreviniz temelinde en verimli yöntemi seçin.

Sonraki adımlar

R modeli oluştur ve SQL'ye kaydet

  • Last updated on