def test_column_comb(self): s = ''' AccountTacticData = SELECT AccountId, OptTypeId, AccountId.ToString() + "_" + OptTypeId.ToString() AS TempOpportunityId, OptTacticMapping.OptAdInsightCategory FROM AccountTacticData LEFT JOIN AccountInfo ON AccountTacticData.AccountId == AccountInfo.AccountId LEFT JOIN AccountLocationMapping ON AccountTacticData.AccountId == AccountLocationMapping.AccountId LEFT JOIN AccountVerticalMapping ON AccountTacticData.AccountId == AccountVerticalMapping.AccountId LEFT JOIN OptTacticMapping ON AccountTacticData.OptTypeId == OptTacticMapping.OptTypeId; ''' result = Select().parse(s) self.assertTrue(result['assign_var'] == 'AccountTacticData') self.assertCountEqual(result['sources'], [ 'AccountTacticData', 'AccountInfo', 'AccountLocationMapping', 'AccountVerticalMapping', 'OptTacticMapping' ])
def test_multiple_select_union(self): s = ''' AdgroupCount = SELECT AccountId, CampaignId FROM AdgroupCountSource UNION ALL SELECT AccountId, NumAdGroups FROM CampaignLevelAdgroupCount UNION ALL SELECT AccountId, (long) -1 AS CampaignId, (long) -1 AS OrderId, NumAdGroups FROM AccountLevelAdgroupCount; ''' result = Select().parse(s) self.assertTrue(result['assign_var'] == 'AdgroupCount') self.assertCountEqual(result['sources'], [ 'AdgroupCountSource', 'CampaignLevelAdgroupCount', 'AccountLevelAdgroupCount' ])
def test_complex_column_multiline(self): s = ''' AuctionWithUpdatedPclick = SELECT OptId, CampaignId, A.RGUID, A.ListingId, A.CPC, (B.Position == NULL ? (AuctionSimulator.SimulateCtx.ActualClicks.Any() && AuctionSimulator.SimulateCtx.Competitors.Values.Where(p=>p.ListingId == (ulong)ListingId).FirstOrDefault()!= NULL ? Math.Min(1, AuctionSimulator.SimulateCtx.ActualClicks.Sum(a => a.GetClicks( AuctionSimulator.SimulateCtx.Competitors.Values.Where(p => p.ListingId == (ulong) ListingId).FirstOrDefault(), Level.ListingLevel, PClick, (int) Position, AuctionSimulator.SimulateCtx.TheAuction.GetPosition((ulong) ListingId)) )) : 0) : B.Clicks) AS PClick FROM AuctionWithUpdatedCPC AS A LEFT OUTER JOIN ListingBidDemand AS B ON A.RGUID == B.RGUID AND A.ListingId == B.ListingId AND A.Position == B.Position; ''' result = Select().parse(s) self.assertTrue(result['assign_var'] == 'AuctionWithUpdatedPclick') self.assertCountEqual(result['sources'], ['AuctionWithUpdatedCPC', 'ListingBidDemand'])
def __init__(self, b_add_sstream_link=False, b_add_sstream_size=False): self.vars = {} self.declare = Declare() self.set = Set() self.input = Input() self.output = Output() self.module = Module() self.process = Process() self.reduce = Reduce() self.combine = Combine() self.using = Using() self.select = Select() self.scope_resolver = ScopeResolver() self.b_add_sstream_link = b_add_sstream_link self.b_add_sstream_size = b_add_sstream_size self.sstream_link_prefix = "" self.sstream_link_suffix = "" self.external_params = {} self.sstream_link_prefix = "" self.sstream_link_suffix = "" self.target_date_str = "" self.default_datetime = DatetimeUtility.get_datetime() if b_add_sstream_size: self.ssu = SstreamUtility( "d:/workspace/dummydummy.ini") # specify your auth file path # read fallback configs from ini file config_filepath = os.path.join(os.path.dirname(__file__), os.pardir, 'config', 'config.ini') self.read_configs(config_filepath)
def test_form_extract(self): s = ''' EmptyManifest = SELECT COUNT() AS C FROM (EXTRACT A:string FROM @EmptyTxt USING DefaultTextExtractor) ''' result = Select().parse(s) self.assertTrue(result['assign_var'] == 'EmptyManifest') self.assertCountEqual(result['sources'], ['EXTRACT_@EmptyTxt'])
def test_column_new_obj(self): s = ''' KWCandidatesWithNewPos = SELECT RGUID, new KeywordOptNode(AccountId, CampaignId, OrderId, OptType, SuggKW, KeyTerm, SuggBid, NewRS, NewPos, NewPClick) AS KWONode FROM KWCandidatesWithNewPos; ''' result = Select().parse(s) self.assertTrue(result['assign_var'] == 'KWCandidatesWithNewPos') self.assertCountEqual(result['sources'], ['KWCandidatesWithNewPos'])
def test_parse_no_assign_no_from(self): s = ''' SELECT DateKey, Domain, SUM(CoImpressionCnt) AS CoImpressionCnt ''' result = Select().parse(s) self.assertTrue(result['assign_var'] is None) self.assertCountEqual(result['sources'], [])
def test_parse_aggr_over(self): s = ''' KWCandidatesWithRS = SELECT *, COUNT() OVER (PARTITION BY OrderId, SuggKW, OptType) AS Srpv FROM KWCandidatesWithRS; ''' result = Select().parse(s) self.assertTrue(result['assign_var'] == 'KWCandidatesWithRS') self.assertCountEqual(result['sources'], ['KWCandidatesWithRS'])
def test_from_one_line_from(self): s = ''' CampaignInRGUIDsWithName = SELECT A.*,B.CampaignName FROM CampaignInRGUIDs AS A LEFT OUTER JOIN NoSLCampaigns AS B ON A.CampaignId == B.CampaignId ''' result = Select().parse(s) self.assertTrue(result['assign_var'] == 'CampaignInRGUIDsWithName') self.assertCountEqual(result['sources'], ['CampaignInRGUIDs', 'NoSLCampaigns'])
def test_parse_if_stmt(self): s = ''' SELECT L.DateKey, IF(L.PositionNum < R.PositionNum, 0, 1) AS AboveCnt, R.TopCnt FROM Table1, Table2 ''' result = Select().parse(s) self.assertTrue(result['assign_var'] is None) self.assertCountEqual(result['sources'], ['Table1', 'Table2'])
def test_column_parentheses(self): s = ''' PositionBoostMarkets = SELECT Market, LIST((CountryCode[0]<< 8) | CountryCode[1]) AS EnabledCountries FROM PositionBoostConfig; ''' result = Select().parse(s) self.assertTrue(result['assign_var'] == 'PositionBoostMarkets') self.assertCountEqual(result['sources'], ['PositionBoostConfig'])
def test_parse_union(self): s = ''' a = SELECT * FROM Step1 UNION ALL SELECT * FROM ImpressionShare ''' result = Select().parse(s) self.assertTrue(result['assign_var'] == 'a') self.assertTrue(len(result['sources']) == 2)
def test_from_view(self): s = ''' RejectionRule = SELECT CustomerId, AccountId FROM (VIEW @RejectRuleView) WHERE CustomizationConditions.Contains("Deduped:Yes") AND TacticCode IN("IN1-I", "BMM-I", "SM1-I", "BMA-I", "SKW-I"); ''' result = Select().parse(s) self.assertTrue(result['assign_var'] == 'RejectionRule') self.assertCountEqual(result['sources'], ['VIEW_@RejectRuleView'])
def test_complex_if_2(self): s = ''' OrderMPISpend = SELECT OrderMPISpend.*, IF(DailyBudgetUSD == null || MPISpend/100.0 <= DailyBudgetUSD, 1.0, DailyBudgetUSD/(MPISpend/100.0)) AS BudgetFactor FROM OrderMPISpend LEFT OUTER JOIN CampaignBudget ON OrderMPISpend.CampaignId == CampaignBudget.CampaignId; ''' result = Select().parse(s) self.assertTrue(result['assign_var'] == 'OrderMPISpend') self.assertCountEqual(result['sources'], ['OrderMPISpend', 'CampaignBudget'])
def test_cast_space_num(self): s = ''' TMAllData = SELECT Keyword, MatchTypeId, (long) - 1 AS CompetitiveIndex FROM TMAllData; ''' result = Select().parse(s) self.assertTrue(result['assign_var'] == 'TMAllData') self.assertCountEqual(result['sources'], ['TMAllData'])
def test_cast_stmt(self): s = ''' TMAllData = SELECT Keyword, MatchTypeId, (double) SUM(Impressions * CompetitiveIndex) / SUM(Impressions) AS CompetitiveIndex FROM TMAllData; ''' result = Select().parse(s) self.assertTrue(result['assign_var'] == 'TMAllData') self.assertCountEqual(result['sources'], ['TMAllData'])
def test_parse_from_input_module_freestyle(self): s = ''' DiagnosticFull = SELECT * FROM DiagnosticModules.DiagnosticPageView(INPUT_BASE = @CommomDataLayerBasePath, END_DATE_UTC = @RunDateTimeUTC) WHERE NOT (Mainline4Reserve == null AND MainlineReserve == null AND ABTestConfigVersion == null AND NumSidebarAds == null) ''' result = Select().parse(s) self.assertTrue(result['assign_var'] == 'DiagnosticFull') self.assertCountEqual(result['sources'], ['MODULE_DiagnosticModules.DiagnosticPageView'])
def test_cross_join(self): s = ''' BadDump = SELECT BMMOptAfterCapping.*, BadKeywordsPM FROM BMMOptAfterCapping CROSS JOIN LinePM; ''' result = Select().parse(s) self.assertTrue(result['assign_var'] == 'BadDump') self.assertCountEqual(result['sources'], ['BMMOptAfterCapping', 'LinePM'])
def test_first_if(self): s = ''' QualityFactorScale = SELECT AdGroupId, FIRST(QualityFactorScale) AS QualityFactorScale, FIRST(IF(double.IsNaN(PClickScale), 1.0, PClickScale)) AS PClickScale FROM (SSTREAM @QualityFactorScale); ''' result = Select().parse(s) self.assertTrue(result['assign_var'] == 'QualityFactorScale') self.assertCountEqual(result['sources'], ['SSTREAM_@QualityFactorScale'])
def test_parse_basic(self): s = ''' ImpressionShare_Campaign = SELECT DateKey, HourNum, 0L AS AboveCnt, 0L AS TopCnt FROM PairAggCampaignAgg; ''' result = Select().parse_assign_select(s) self.assertTrue(len(result) > 0) self.assertTrue(result['assign_var'] == 'ImpressionShare_Campaign') self.assertTrue(result['from'][1] == 'PairAggCampaignAgg')
def test_column_double_question_mark_M(self): s = ''' Campaigns = SELECT A.*, B.SpendUSD ?? 0M AS SpendUSD FROM Campaigns AS A LEFT OUTER JOIN CampaignSpending AS B ON A.CampaignId == B.CampaignId; ''' result = Select().parse(s) self.assertTrue(result['assign_var'] == 'Campaigns') self.assertCountEqual(result['sources'], ['Campaigns', 'CampaignSpending'])
def test_column_ident_dot(self): s = ''' PageData = SELECT DISTINCT Monetization_PageView.* FROM Monetization_PageView LEFT OUTER JOIN Diagnostic AS B ON Monetization_PageView.RGUID == B.RGUID; ''' result = Select().parse(s) self.assertTrue(result['assign_var'] == 'PageData') self.assertCountEqual(result['sources'], ['Monetization_PageView', 'Diagnostic'])
def test_from_sstream_streamset(self): s = ''' MPI = SELECT *, __serialnum AS N FROM (SSTREAM STREAMSET @MPI_PATH PATTERN @"KeywordOptMPIFinal%n.ss" RANGE __serialnum=["0", "6"] ); ''' result = Select().parse(s) self.assertTrue(result['assign_var'] == 'MPI') self.assertCountEqual(result['sources'], ['SSTREAM<STREAMSET>_@MPI_PATH'])
def test_parse_from_input_sstream(self): s = ''' AdDispayUrl = SELECT ulong.Parse(AdId) AS AdId, long.Parse(ListingId) AS ListingId, Domain FROM ( SSTREAM @FinalDomainPath ) ''' result = Select().parse(s) self.assertTrue(result['assign_var'] == 'AdDispayUrl') self.assertCountEqual(result['sources'], ['SSTREAM_@FinalDomainPath'])
def test_column_nested_func(self): s = ''' AllLevelPerf = SELECT Microsoft.RnR.AdInsight.Utils.ConvertToInt(SUM(IF(ALL(ValidImpressions > 0, Utility.AbsPositionFromPagePosition(PagePosition, Markets, LCID, MarketplaceClassificationId, PublisherOwnerId, CountryCode, MLAdsCnt, RequestedMainlineAdsCnt) <= 4), 1, 0))) AS MLImpressions, (double)SUM(ConversionCnt) AS Conversions FROM BillableListings CROSS JOIN PositionBoostConfig HAVING LCID >= 0 AND MatchTypeId >= 0 AND MLImpressions >= 0 AND MLImpressionsWithoutAdjust >= 0; ''' result = Select().parse(s) self.assertTrue(result['assign_var'] == 'AllLevelPerf') self.assertCountEqual(result['sources'], ['BillableListings', 'PositionBoostConfig'])
def test_column_func_lambda(self): s = ''' BidHistoryRecord = SELECT OrderItemId, History.Split(';').Select(a => new BidHistory(a)).ToList() AS History FROM ( SSTREAM @BidHistory ) WHERE NOT string.IsNullOrEmpty(History); ''' result = Select().parse(s) self.assertTrue(result['assign_var'] == 'BidHistoryRecord') self.assertCountEqual(result['sources'], ['SSTREAM_@BidHistory'])
def test_parse_semijoin(self): s = ''' ListingBidDemand = SELECT A.* FROM (SSTREAM @ListingBidDemand) AS A LEFT SEMIJOIN AuctionContextOnlyWithTA ON A.RGUID == AuctionContextOnlyWithTA.RGUID; ''' result = Select().parse(s) self.assertTrue(result['assign_var'] == 'ListingBidDemand') self.assertCountEqual( result['sources'], ['SSTREAM_@ListingBidDemand', 'AuctionContextOnlyWithTA'])
def test_union_distinct(self): s = ''' BadAccounts = SELECT AccountId, (int) 2 AS TrafficId FROM AggregatorList WHERE bAggregator==true UNION DISTINCT SELECT AccountId, (int) 2 AS TrafficId FROM SpamAccountList; ''' result = Select().parse(s) self.assertTrue(result['assign_var'] == 'BadAccounts') self.assertCountEqual(result['sources'], ['AggregatorList', 'SpamAccountList'])
def test_parse_inner_join_two_table(self): s = ''' data = SELECT L.DateKey, R.PositionNum, IF(L.PositionNum < R.PositionNum, 0, 1) AS AboveCnt, R.TopCnt FROM LeftTable AS L INNER JOIN RightTable AS R ON L.RGUID == R.RGUID WHERE L.OrderItemId != R.OrderItemId; ''' result = Select().parse(s) self.assertTrue(result['assign_var'] == 'data') self.assertCountEqual(result['sources'], ['LeftTable', 'RightTable'])
def test_complex_expr_column(self): s = ''' ExchangeRateMap = SELECT CurrencyInfo.CurrencyId, ExchangeRateUSD, (int) Math.Ceiling(MinBid * (ExchangeRateUSD ?? 1m) * 100 - 0.01m) AS MinBid FROM CurrencyInfo LEFT OUTER JOIN ExchangeRates ON ExchangeRates.CurrencyId == CurrencyInfo.CurrencyId; ''' result = Select().parse(s) self.assertTrue(result['assign_var'] == 'ExchangeRateMap') self.assertCountEqual(result['sources'], ['CurrencyInfo', 'ExchangeRates'])