The below will build a table in SQL to store Refresh times. The first time
it runs it will put in an entry and going backwards in time until all
records are retrieved. Once compete it will retrieve based on fields
lastmodifieddate and createddate. The time based chunks allow ES enough
time to process based on your window. Replace your tables time fields with
lastmodifieddate and createddate. This could be genericized to pass in the
field names of LMD and CD.
In your SQL DB create a table to hold your refreshtime data:
ElasticSearchRefreshTime
CREATE TABLE [dbo].[ElasticSearchRefreshTime](
[id] [int] IDENTITY(1,1) NOT NULL,
[DataObjectID] [nvarchar](100) NOT NULL,
[LastRefreshDateTime] [datetime2](7) NULL,
[RebuildSearchIndex] [bit] NOT NULL CONSTRAINT
[DF_Metadata_ElasticSearchRefreshTime_RebuildSearchIndex] DEFAULT ((1)),
[PreviousRunTime] [datetime2](7) NULL,
[NumberofExecutionsSinceRebuild] [bigint] NOT NULL CONSTRAINT
[DF_Metadata_ElasticSearchRefreshTime_NumberofExecutionsSinceRebuild]
DEFAULT ((0)),
[RebuildMonth] [int] NOT NULL CONSTRAINT
[DF_Metadata_ElasticSearchRefreshTime_RebuildPageSize] DEFAULT ((0)),
[RebuildYear] [int] NOT NULL CONSTRAINT
[DF_Metadata_ElasticSearchRefreshTime_RebuildTransferPostion] DEFAULT
((0)),
[RebuildTotalCompleted] [bigint] NOT NULL CONSTRAINT
[DF_Metadata_ElasticSearchRefreshTime_RebuildTotalCompleted] DEFAULT ((0)),
[InProcess] [bit] NOT NULL CONSTRAINT
[DF_Metadata_ElasticSearchRefreshTime_InProcess] DEFAULT ((0)),
CONSTRAINT [PK_Metadata_ElasticSearchRefreshTime_1] PRIMARY KEY CLUSTERED
(
[DataObjectID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
Then create your procedure:
Create PROCEDURE [dbo].[ESTransport]
@EntityName as nvarchar(50)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
declare @lastrefreshdatetime datetime2
declare @RebuildSearchIndex bit
declare @SprocEntryTime Datetime2 = SYSDATETIME()
declare @RebuildMonth int = 0
declare @RebuildYear int = 0
declare @InProcess bit = 0
declare @TotalRows bigint = 0
declare @RebuildTotalCompleted bigint = 0
declare @MaxDate Datetime2
-- select count(1),max(createddate) from [Case]
declare @TotalSQL nvarchar(200)
declare @ParmDefinition nvarchar(255)
SET @TotalSQL = 'select @TotalRowsOut = count(1), @MaxDateOut =
Max(CreatedDate) from [' + @EntityName +'] where isDeleted=''false'''
SET @ParmDefinition = N'@TotalRowsOut bigint OUTPUT,
@MaxDateOut datetime2 OUTPUT'
EXECUTE sp_executesql
@TotalSQL,
@ParmDefinition,
@TotalRowsOut = @TotalRows OutPut,
@MaxDateOut = @MaxDate OUTPUT
PRINT @TotalRows
PRINT @MaxDate
select
@lastrefreshdatetime=[LastRefreshDateTime],@RebuildMonth=RebuildMonth,
@RebuildYear=RebuildYear, @InProcess =
InProcess,@RebuildSearchIndex=[RebuildSearchIndex],@RebuildTotalCompleted=RebuildTotalCompleted
from [Metadata_ElasticSearchRefreshTime] where DataObjectID=@EntityName
IF @lastrefreshdatetime is null
BEGIN
SET @RebuildSearchIndex=1
SET @InProcess = 0
INSERT INTO [dbo].[Metadata_ElasticSearchRefreshTime]
([DataObjectID]
,[LastRefreshDateTime]
,[RebuildSearchIndex]
,[NumberofExecutionsSinceRebuild]
,[RebuildMonth]
,[RebuildYear]
,[RebuildTotalCompleted]
,[InProcess])
VALUES
(@EntityName
,@SprocEntryTime
,@RebuildSearchIndex
,0
,MONTH(@MaxDate)
,YEAR(@MaxDate)
,0
,@InProcess)
END
-- Begin statements
IF @EntityName = 'Contact' and @RebuildSearchIndex = 1
BEGIN
IF @InProcess=0
BEGIN
--First Pass here Direction is descending
SET @InProcess=1
SET @RebuildMonth = Month(@MaxDate)
SET @RebuildYear = Year(@MaxDate)
END
ELSE IF @InProcess=1
BEGIN
--If you hit January drop to December of the prior year
IF @RebuildMonth=1
BEGIN
SET @RebuildMonth = 12
SET @RebuildYear = @RebuildYear-1
END
ELSE
BEGIN
SET @RebuildMonth =@RebuildMonth-1
END
END
SELECT Entity.id as _id,Entity.*
FROM [MyDB].[dbo].[Entity] WITH (NOLOCK)
and Month(contact.CreatedDate)=@RebuildMonth and Year(contact.CreatedDate)
= @RebuildYear
UPDATE [dbo].[Metadata_ElasticSearchRefreshTime]
SET [DataObjectID] = @EntityName
,[LastRefreshDateTime] = @SprocEntryTime
,[RebuildSearchIndex] = CASE WHEN ((@RebuildTotalCompleted +
@@ROWCOUNT)<@TotalRows) THEN 1 ELSE 0 END
,[RebuildMonth] = @RebuildMonth
,[RebuildYear] = @RebuildYear
,[PreviousRunTime] = @lastrefreshdatetime
,[NumberofExecutionsSinceRebuild] = 0
,[InProcess] = CASE WHEN ((@RebuildTotalCompleted +
@@ROWCOUNT)<@TotalRows) THEN 1 ELSE 0 END
,[RebuildTotalCompleted] = CASE WHEN (@RebuildTotalCompleted=0) THEN
@@ROWCOUNT ELSE @RebuildTotalCompleted+@@ROWCOUNT END
WHERE DataObjectID=@EntityName
END
ELSE IF @EntityName = 'Entity'
BEGIN
UPDATE [dbo].[Metadata_ElasticSearchRefreshTime]
SET [DataObjectID] = @EntityName
,[LastRefreshDateTime] = @SprocEntryTime
,[RebuildSearchIndex] = 0
,PreviousRunTime = @lastrefreshdatetime
,NumberofExecutionsSinceRebuild = NumberofExecutionsSinceRebuild+1
WHERE DataObjectID=@EntityName
SELECT Entity.id as _id,Entity.*
FROM [MyDB].[dbo].[Entity] WITH (NOLOCK)
and ([Contact].LastModifiedDate >= @lastrefreshdatetime or
[Contact].CreatedDate >=@lastrefreshdatetime)
END
END
Finally in Sense I create the river (i used integrated security):
PUT _river/Entity/_meta
{
"type":"jdbc",
"jdbc": {
"url":"jdbc:sqlserver://dbserver.mydomain.com:1433;databaseName=MyDB;integratedSecurity=true;",
"sql":{
"callable" : true,
"statement" : "{call ESTransport(?)}",
"parameter" : "Entity"
},
"schedule":"0/30 0-59 0-23 ? * *",
"autocommit":true,
"index":"jdbc",
"type":"Entity"
}
}
On Wednesday, April 29, 2015 at 7:42:33 AM UTC-5, Jörg Prante wrote:
>
> Nice work, can you share the recipe with the community?
>
> I could post it on the JDBC plugin wiki
>
> Jörg
>
> On Wed, Apr 29, 2015 at 1:56 PM, GWired <garrett...@gmail.com
> <javascript:>> wrote:
>
>> My theory is that i was overloading my ES VM's on initial loads or when
>> doing large loads.
>>
>> My cpu would jump to 99% and during the pulls it would fail silently and
>> the river thought it got all the documents but didn't.
>>
>> I have since rewritten my rivers using Stored procedures instead and it
>> pulls much faster and CPU doesn't go to high.
>>
>> The procedure keeps track of the created_at and updated_at for me instead
>> of using the column strategy. I also chunked my initial calls into logical
>> batches. Mine happen to be date based by month. I couldn't figure out a
>> way that made sense using row sizes or anything and month seemed just as
>> good as any and it works.
>>
>> This also solved my problem of when / ES is turned off. It now will just
>> start back up where it last left off because SQL server is storing that
>> info instead of ES. I also made it so I can rebuild at will.
>>
>> Thanks Garrett
>>
>>
>>
>> On Thursday, April 23, 2015 at 2:48:03 AM UTC-4, Jörg Prante wrote:
>>>
>>> There are log messages at ES cluster side, you should look there why
>>> bulk indexing failed.
>>>
>>> Jörg
>>>
>>> On Thu, Apr 23, 2015 at 5:45 AM, GWired <garrett...@gmail.com> wrote:
>>>
>>>> Found this in the logs:
>>>>
>>>> [2015-04-22 22:01:25,063][ERROR][river.jdbc.BulkNodeClient] bulk [15]
>>>> failed with 945 failed items, failure message = failure in bulk execution:
>>>>
>>>>
>>>>
>>>> On Wednesday, April 22, 2015 at 7:53:25 PM UTC-5, GWired wrote:
>>>>>
>>>>> Hi All,
>>>>>
>>>>> I've just been informed that i'm off by up to 100k records or so in my
>>>>> jdbc river fed index.
>>>>>
>>>>> I am using the column strategy using a createddate and lastmodified
>>>>> date.
>>>>>
>>>>> Kibana is reporting an entirely different # than what i see reported
>>>>> in the DB..
>>>>>
>>>>> Table A has 978634 in SQL, 934646 shown in Kibana.
>>>>> Table B has 957327 in SQL, 876725 shown in Kibana.
>>>>> Table C has 312826 in SQL, 238534 shown in Kibana
>>>>>
>>>>> I see in the ES logs
>>>>>
>>>>> Table A metrics: 979044 rows,
>>>>> Table B metrics: 957591 rows
>>>>> Table C metrics: 312827 rows,
>>>>>
>>>>> These are the right numbers...well at least closer to right.
>>>>>
>>>>> But if i do this using Sense:
>>>>>
>>>>> GET jdbc/mytable/_count?q=*
>>>>>
>>>>> It returns the same # as Kibana is return.
>>>>>
>>>>> This erring version is running on ES 1.5.1 with Kibana version 3.0
>>>>>
>>>>> On another server with ES 1.5.0 and Kibana 3.0 it is working just fine
>>>>> #'s match up.
>>>>>
>>>>> Any ideas?
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>> --
>>>> You received this message because you are subscribed to the Google
>>>> Groups "elasticsearch" group.
>>>> To unsubscribe from this group and stop receiving emails from it, send
>>>> an email to elasticsearc...@googlegroups.com.
>>>> To view this discussion on the web visit
>>>> https://groups.google.com/d/msgid/elasticsearch/5561753d-9553-4bc5-bea2-102b7e030396%40googlegroups.com
>>>>
>>>> <https://groups.google.com/d/msgid/elasticsearch/5561753d-9553-4bc5-bea2-102b7e030396%40googlegroups.com?utm_medium=email&utm_source=footer>
>>>> .
>>>>
>>>> For more options, visit https://groups.google.com/d/optout.
>>>>
>>>
>>> --
>> You received this message because you are subscribed to the Google Groups
>> "elasticsearch" group.
>> To unsubscribe from this group and stop receiving emails from it, send an
>> email to elasticsearc...@googlegroups.com <javascript:>.
>> To view this discussion on the web visit
>> https://groups.google.com/d/msgid/elasticsearch/5c2016b5-ef5f-4f54-ac37-b2991eba0775%40googlegroups.com
>>
>> <https://groups.google.com/d/msgid/elasticsearch/5c2016b5-ef5f-4f54-ac37-b2991eba0775%40googlegroups.com?utm_medium=email&utm_source=footer>
>> .
>>
>> For more options, visit https://groups.google.com/d/optout.
>>
>
>
--
You received this message because you are subscribed to the Google Groups
"elasticsearch" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to elasticsearch+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/elasticsearch/ca6554c1-5cd9-46e2-af65-6c12a2bd23f9%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
