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_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_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 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_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'])
