Exemplo n.º 1
0
        private void MoveChunkDataToS3()
        {
            var chunkIds = _dbChunk.GetNotMovedToS3Chunks(Settings.Settings.Current.Building.Id.Value).ToArray();

            if (chunkIds.Length == 0)
            {
                return;
            }

            var baseFolder =
                $"{Settings.Settings.Current.Bucket}/{Settings.Settings.Current.Building.Vendor}/{Settings.Settings.Current.Building.Id}/raw";

            Console.WriteLine("S3 raw folder - " + baseFolder);

            Parallel.ForEach(Settings.Settings.Current.Building.SourceQueryDefinitions, queryDefinition =>
            {
                if (queryDefinition.Providers != null)
                {
                    return;
                }
                if (queryDefinition.Locations != null)
                {
                    return;
                }
                if (queryDefinition.CareSites != null)
                {
                    return;
                }

                var sql = GetSqlHelper.GetSql(Settings.Settings.Current.Building.SourceEngine.Database,
                                              queryDefinition.GetSql(Settings.Settings.Current.Building.Vendor,
                                                                     Settings.Settings.Current.Building.SourceSchemaName),
                                              Settings.Settings.Current.Building.SourceSchemaName);

                if (string.IsNullOrEmpty(sql))
                {
                    return;
                }

                sql = string.Format(sql, chunkIds[0]);

                if (queryDefinition.FieldHeaders == null)
                {
                    StoreMetadataToS3(queryDefinition, sql);
                }
            });

            Parallel.ForEach(chunkIds, new ParallelOptions {
                MaxDegreeOfParallelism = 2
            }, cId =>
            {
                var chunkId = cId;

                Parallel.ForEach(Settings.Settings.Current.Building.SourceQueryDefinitions,
                                 new ParallelOptions {
                    MaxDegreeOfParallelism = 5
                }, queryDefinition =>
                {
                    try
                    {
                        if (queryDefinition.Providers != null)
                        {
                            return;
                        }
                        if (queryDefinition.Locations != null)
                        {
                            return;
                        }
                        if (queryDefinition.CareSites != null)
                        {
                            return;
                        }

                        var sql = GetSqlHelper.GetSql(Settings.Settings.Current.Building.SourceEngine.Database,
                                                      queryDefinition.GetSql(Settings.Settings.Current.Building.Vendor,
                                                                             Settings.Settings.Current.Building.SourceSchemaName),
                                                      Settings.Settings.Current.Building.SourceSchemaName);

                        if (string.IsNullOrEmpty(sql))
                        {
                            return;
                        }

                        sql = string.Format(sql, chunkId);

                        //if (queryDefinition.FieldHeaders == null)
                        //{
                        //    StoreMetadataToS3(queryDefinition, sql);
                        //}

                        var personIdField = queryDefinition.GetPersonIdFieldName();
                        var tmpTableName  = "#" + queryDefinition.FileName + "_" + chunkId;


                        var folder   = $"{baseFolder}/{chunkId}/{queryDefinition.FileName}";
                        var fileName = $@"{folder}/{queryDefinition.FileName}";

                        var unloadQuery = string.Format(@"create table {0} sortkey ({1}) distkey ({1}) as {2}; " +
                                                        @"UNLOAD ('select * from {0} order by {1}') to 's3://{3}' " +
                                                        @"DELIMITER AS '\t' " +
                                                        @"credentials 'aws_access_key_id={4};aws_secret_access_key={5}' " +
                                                        @"GZIP ALLOWOVERWRITE PARALLEL ON",
                                                        tmpTableName,                                    //0
                                                        personIdField,                                   //1
                                                        sql,                                             //2
                                                        fileName,                                        //3
                                                        Settings.Settings.Current.S3AwsAccessKeyId,      //4
                                                        Settings.Settings.Current.S3AwsSecretAccessKey); //5

                        using (var connection =
                                   SqlConnectionHelper.OpenOdbcConnection(Settings.Settings.Current.Building
                                                                          .SourceConnectionString))
                            using (var c = new OdbcCommand(unloadQuery, connection))
                            {
                                c.CommandTimeout = 999999999;
                                c.ExecuteNonQuery();
                            }
                    }
                    catch (Exception e)
                    {
                        Logger.WriteError(chunkId, e);
                        throw;
                    }
                });

                _dbChunk.ChunkCreated(chunkId, Settings.Settings.Current.Building.Id.Value);
                Console.WriteLine("Raw data for chunkId=" + chunkId + " is available on S3");
            });
        }