Вопрос
Я пытаюсь вставить данные с помощью XML-файлов и SqlBulkCopy. Таблица назначения представляет собой таблицу временных рядов, созданную ниже
create table TimeSeries (
Id uniqueidentifier constraint DF_TimeSeries_Id default (newid()) not null,
ObjectId uniqueidentifier not null,
[Date] datetime not null,
Value float(53) not null,
[Type] varchar (4) not null,
[Source] varchar (4) not null,
LastUpdate datetime constraint DF_TimeSeries_LastUpdate default (getdate()) not null,
TypeIndex smallint constraint DF_TimeSeries_TypeIndex default (0) not null,
constraint PK_TimeSeries primary key clustered ([Date] asc, ObjectId asc, [Type] asc, [Source] asc, TypeIndex asc) with (fillfactor = 80)
);
go
create nonclustered index [IX_TimeSeries_ObjectId_Type_Date_Source]
on TimeSeries(ObjectId asc, [Type] asc, [Date] asc, [Source] asc)
include(Value) with (fillfactor = 80);
go
create nonclustered index [IX_TimeSeries_ObjectId_Date]
on TimeSeries(ObjectId asc, [Date] asc)
include(Value) with (fillfactor = 80);
go
create table Beacons
(
BeaconId uniqueidentifier not null default newid(),
[Description] varchar(50) not null,
LocationX float not null,
LocationY float not null,
Altitude float not null
constraint PK_Beacons primary key clustered (BeaconId)
)
go
create index IX_Beacons on Beacons (BeaconId)
go
create table SnowGauges
(
SnowGaugeId uniqueidentifier not null default newid(),
[Description] varchar(50) not null
constraint PK_SnowGauges primary key clustered (SnowGaugeId)
)
go
create index IX_SnowGauges on SnowGauges (SnowGaugeId)
go
insert into Beacons ([Description], LocationX, LocationY, Altitude)
values ('Dunkery', 51.162, -3.586, 519), ('Prestwich', 53.527, -2.279, 76)
insert into SnowGauges ([Description]) values ('Val d''Isère')
select * from Beacons
select * from SnowGauges
Как вы можете видеть, я хочу хранить в TimeSeries любые временные ряды. Это может быть температура, давление, биологические данные и т. Д. В любом случае, я могу определить временные ряды с помощью уникального идентификатора, источника и типа. В ObjectId нет внешнего ключа, поскольку этот уникальный идентификатор может ссылаться на любую таблицу.
В конце этого сценария я вставил 2 маяка и один снегоход, и я хочу заполнить их временные ряды. Файл XML для этого имеет такой формат:
<?xml version="1.0" encoding="utf-8" ?>
<TimeSeries>
<TimeSeries ObjectId="186CA33E-AC1C-4220-81DE-C7CD32F40C1A" Date="09/06/2013 07:00:00" Value="9.2" Source = "Met Office" Type = "Temperature"/>
<TimeSeries ObjectId="186CA33E-AC1C-4220-81DE-C7CD32F40C1A" Date="09/06/2013 10:00:00" Value="8.8" Source = "Met Office" Type = "Temperature"/>
<TimeSeries ObjectId="186CA33E-AC1C-4220-81DE-C7CD32F40C1A" Date="09/06/2013 13:00:00" Value="8.7" Source = "Met Office" Type = "Temperature"/>
<TimeSeries ObjectId="186CA33E-AC1C-4220-81DE-C7CD32F40C1A" Date="09/06/2013 07:00:00" Value="1" Source = "Met Office" Type = "UV"/>
<TimeSeries ObjectId="186CA33E-AC1C-4220-81DE-C7CD32F40C1A" Date="09/06/2013 10:00:00" Value="3" Source = "Met Office" Type = "UV"/>
<TimeSeries ObjectId="186CA33E-AC1C-4220-81DE-C7CD32F40C1A" Date="09/06/2013 13:00:00" Value="5" Source = "Met Office" Type = "UV"/>
<TimeSeries ObjectId="AFB81E51-18B0-4696-9C2F-E6E9EEC1B647" Date="09/06/2013 07:00:00" Value="5.8" Source = "Met Office" Type = "Temperature"/>
<TimeSeries ObjectId="AFB81E51-18B0-4696-9C2F-E6E9EEC1B647" Date="09/06/2013 10:00:00" Value="6.3" Source = "Met Office" Type = "Temperature"/>
<TimeSeries ObjectId="AFB81E51-18B0-4696-9C2F-E6E9EEC1B647" Date="09/06/2013 13:00:00" Value="6.5" Source = "Met Office" Type = "Temperature"/>
<TimeSeries ObjectId="50E52A2B-D719-4341-A451-110D0874D26D" Date="07/06/2013 00:00:00" Value="80.5" Source = "Meteo France" Type = "SnowMeter"/>
<TimeSeries ObjectId="50E52A2B-D719-4341-A451-110D0874D26D" Date="08/06/2013 00:00:00" Value="80.5" Source = "Meteo France" Type = "SnowMeter"/>
</TimeSeries>
Если вы запустите первый скрипт, вы можете ожидать, что у него будет другой ObjectId, и вам придется обновлять его в XML-файле. Поэтому оттуда все должно быть прямолинейным, и простая программа C # должна выполнить задание по вставке данных. Давайте посмотрим на код C #:
using System;
using System.Data;
using System.Data.SqlClient;
using System.IO;
namespace XMLBulkInsert
{
class Program
{
const string XMLFILE_PATH = @"C:\Workspaces\Ws1\R\TimeSeries\TimeSeries.xml";
const string CONNECTION_STRING = @"Server=RISK1;Database=DevStat;Trusted_Connection=True;";
static void Main(string[] args)
{
StreamReader xmlFile = new StreamReader(XMLFILE_PATH);
DataSet ds = new DataSet();
Console.Write("Read file... ");
ds.ReadXml(xmlFile);
DataTable sourceData = ds.Tables[0];
Console.WriteLine("Done !");
using (SqlConnection sourceConnection = new SqlConnection(CONNECTION_STRING))
{
sourceConnection.Open();
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sourceConnection.ConnectionString))
{
bulkCopy.ColumnMappings.Add("ObjectId", "ObjectId");
bulkCopy.ColumnMappings.Add("Date", "Date");
bulkCopy.ColumnMappings.Add("Value", "Value");
bulkCopy.ColumnMappings.Add("Source", "Source");
bulkCopy.ColumnMappings.Add("Type", "Type");
bulkCopy.DestinationTableName = "TimeSeries";
try
{
Console.Write("Insert data... ");
bulkCopy.WriteToServer(sourceData);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
finally
{
xmlFile.Close();
sourceConnection.Close();
}
}
}
Console.WriteLine("Insertion completed, please Press Enter...");
Console.ReadLine();
}
}
}
Выполнение этой программы возвращает это исключение: «Данное значение типа String из источника данных не может быть преобразовано в тип uniqueidentifier указанного столбца цели». Похоже, что нет способа установить сопоставление, чтобы заставить столбец быть уникальным идентификатором. Я даже пытался вставить этот код ds.Tables[0].Columns[0].DataType = typeof(Guid); но без успеха, .Net не может изменить тип столбца, как только таблица будет иметь данные строки.
У меня было высокое исключение с SQlBulkCopy, но теперь я чувствую себя немного застрявшим. У меня есть миллионы и миллионы данных в формате XML, и я не могу вставить их из-за этого уникального идентификатора.
Кто-нибудь знает, как настроить этот класс, чтобы принять уникальный идентификатор?
Принятый ответ
Учитывая комментарии около 300 миллионов строк, я бы забыл о DataTable ; вы не хотите загружать эти данные одновременно. Идеальным было бы разобрать его поэтапно, подвергая данные как IDataReader .
К счастью, некоторые утилиты для этого существуют. Во-первых, давайте разобраем данные. Каждая строка по существу:
class TimeSeries
{
public Guid ObjectId { get; set; }
public DateTime Date { get; set; }
public string Source { get; set; }
public string Type { get; set; }
public decimal Value { get; set; }
}
и мы можем написать читатель на основе элементов, например:
static IEnumerable<TimeSeries> ReadTimeSeries(TextReader source)
{
using (var reader = XmlReader.Create(source, new XmlReaderSettings {
IgnoreWhitespace = true }))
{
reader.MoveToContent();
reader.ReadStartElement("TimeSeries");
while(reader.Read() && reader.NodeType == XmlNodeType.Element
&& reader.Depth == 1)
{
using (var subtree = reader.ReadSubtree())
{
var el = XElement.Load(subtree);
var obj = new TimeSeries
{
ObjectId = (Guid) el.Attribute("ObjectId"),
// note: datetime is not xml format; need to parse - this
// should probably be more explicit
Date = DateTime.Parse((string) el.Attribute("Date")),
Source = (string) el.Attribute("Source"),
Type = (string)el.Attribute("Type"),
Value = (decimal)el.Attribute("Value")
};
yield return obj;
}
}
}
}
Обратите внимание, что это «блок-блок итератора» и является ленивым спулингом - он не загружает сразу все данные.
Далее нам нужен API, который может потреблять IEnumerable<T> и показывать его как IDataReader - FastMember делает именно это (и многое другое). Поэтому мы можем просто написать:
using(var bcp = new SqlBulkCopy(connection))
using(var objectReader = ObjectReader.Create(ReadTimeSeries(source)))
{
bcp.DestinationTableName = "SomeTable";
bcp.WriteToServer(objectReader);
}
где source является TextReader , например, из File.OpenText :
using(var source = File.OpenText(path))
using(var bcp = new SqlBulkCopy(connection))
using(var objectReader = ObjectReader.Create(ReadTimeSeries(source)))
{
bcp.DestinationTableName = "SomeTable";
bcp.WriteToServer(objectReader);
}
Если вы хотите управлять порядком столбцов, вы можете использовать bcp.ColumnMappings - но, возможно, более удобным является заставить IDataReader делать это внутри:
using(var objectReader = ObjectReader.Create(
ReadTimeSeries(source, "ObjectId", "Date", "Value" /* etc */)))
{
bcp.DestinationTableName = "SomeTable";
bcp.WriteToServer(objectReader);
}
Я использую это для некоторых своих собственных кодов - даже когда данные действительно вписываются в память, это намного быстрее, чем через DataTable .
Главное, однако, в том, что мы сейчас контролируем происходящее.
Лицензировано согласно: CC-BY-SA with attribution
Не связан с Stack Overflow