SigParser is a product for businesses to quickly turn their email server into a valuable set of insights and contact details

SigParser

Recommendations by Concise User Profiles from Review Text: Experimental Results

Authors: (1) Yunshan Ma, National University of Singapore; (2) Xiaohao Liu, University of Chinese Academy of Sciences; (3) Yinwei Wei, Monash University; (4) Zhulin Tao, Communication University of China and a Corresponding author; (5) Xiang Wang, University of Science and Technology of China and affiliated with Institute of Artificial Intelligence, Institute of Dataspace, Hefei Comprehensive National Science Center; (6) Tat-Seng Chua, National University of Singapore. Table of Links Abstract & Introduction Methodology Experiments Related Work Conclusion and Future Work, Acknowledgment and References 3 EXPERIMENTS We evaluate our proposed methods on two application domains of product bundling, i.e., fashion outfit and music playlist. We are particularly interested in answering the research questions as follow: • RQ1: Does the proposed CLHE method beat the leading methods? • RQ2: Are the key modules, i.e., hierarchical transformer and contrastive learning, effective? • RQ3: How does out method work in countering the problems of cold-start items, modality missing, noise and sparsity of partial bundle? How the detailed configurations affect its performance and how about the computation complexity? 3.1 Experimental Settings There are various application scenarios that are suitable for product bundling, such as e-commerce, travel package, meal, etc., each of which has one or multiple public datasets. However, only datasets that include all the multimodal item features, user feedback data, and bundle data can be used to evaluate our method. Therefore, we choose two representative domains, i.e., fashion outfit and music playlist. We use the POG [11] for fashion outfit. For the music playlist, we use the Spotify [7] dataset for the bundle-item affiliations, and we acquire the user feedback data from the Last.fm dataset [2]. Since the average bundle size is quite small in POG (it makes sense for fashion outfit), we re-sample a second version POG_dense which has denser user feedback connections for each item. In contrast, the average bundle size in Spotify dataset is large, thus we sample a sparser version Spotify_sparse, which has smaller average bundle size. To be noted, we keep the integrity of all the bundles in all the versions, which means we do not corrupt any bundles during the sampling. For each dataset, we randomly split all the bundles into training/validation/testing set with the ratio of 8:1:1. The statistics of the datasets are shown in Table 1. We use the popular ranking protocols of Recall@K and NDCG@K as the evaluation metric, where K=20. Due to the new formulation of our work, there are no previous works that have exactly same setting with ours. Therefore, we pick several leading methods and adapt them to our settings. For fair comparison, all the baseline methods use all the three types of extracted features that are same with our method. In addition, they all use the same negative log-likelihood loss function. 3.1.1 Compared Methods. [51] is an auto-encoder model which uses an average pooling to aggregate the items’ representations to get the bundle representation. • MultiDAE [31] is an variational auto-encoder model which employ the variational inference on top of the MultiDAE method. • MultiVAE [21] treats each bundle as a sequence and uses bidirectional LSTM to learn the bundle representation. • Hypergraph [54] formulates each bundle a hyper-graph and devises a GCN model to learn the bundle representation. • Bi-LSTM [3, 46] tailors a transformer to capture the item interactions and generate the bundle representation. • Transformer is the version that we add bundle-level contrastive loss to the above Transformer model. • TransformerCL 3.2 Overall Performance Comparison (RQ1) Table 2 shows the overall performance comparison between our model CLHE and the baseline methods. We have the following observations. First, our method beats all the baselines on all the datasets, demonstrating the competitive performance of our model. Second, over the baselines, Transformer and TransformerCL achieve the best performance, showing that the self-attention mechanism and contrastive learning can well preserve the correlations among items within the bundle, thus yielding good bundle representations. Third, comparing the results between different versions of dataset, we find that: 1) the performance on POG_dense is much larger than that on POG due to denser user-item interactions, demonstrating that user feedback information is quite helpful to the performance; 2) the performance of Spotify_sparse is relatively smaller than that on Spotify since the sparser bundle-item affiliation data, justifying our hypothesis that large-scale and high-quality bundle dataset is vital to bundle construction. Finally, we have an interesting observation that the performance improvements on the four datasets is negatively correlated with "#Avr.B/I", as shown in Table 1, in another word, in scenarios that items are included in fewer bundles (i.e., the dataset include more sparse items), our method performs even better. This phenomenon further justifies the advantage of our method in countering the issue of sparse items. 3.3 Ablation Study of Key Modules (RQ2) To further evaluate the effectiveness of the key modules of our model, we conduct a list of ablation studies and the results are shown in Table 3. First and foremost, we aim to justify the effectiveness of the user feedback features. Thereby, we remove the user feedback features from our model (i.e., remove p𝑖 from f𝑖 ) and build an ablated version of model, i.e., w/o user feedback. According to the result in Table 3, after removing user feedback features, the performance reduces clearly, verifying that user feedback feature is significant for bundle construction. Second, we would like to evaluate whether each component of the hierarchical encoder is useful. We progressively remove the two self-attention modules from our model and replace them with an vanilla average pooling, thus yielding three ablated models, i.e., w/o item, w/o bundle, and w/o both. The results in Table 3 show that the removal of either selfattention modules causes performance drop. These results further verify the efficacy of our self-attention-based hierarchical encoder framework. Third, to justify the contribution of contrastive learning, we progressively remove the two levels of contrastive loss, thus generating three ablations, i.e., w/o item, w/o bundle, and w/o both. Table 3 depicts the results, which demonstrate the both contrastive losses are helpful, especially on the sparser version of datasets. 3.4 Model Study (RQ3) To explicate more details and various properties of our method, we further conduct a list of model studies. One of the main challenges for bundle construction is cold-start items that have never been included in previous bundles. It is difficult to directly evaluate the methods solely based on cold-start items since there are few testing bundles where both the input and result partial bundles purely consist of coldstart items. Nevertheless, we come up with an alternative way to indirectly test how these methods perform against cold-start items. Specifically, we remove all the cold-start items and just keep the warm items in the testing set, i.e., the warm setting. We test our method and all the baseline models on this warm setting. The results shown in Table 4 illustrate that: 1) the performance of all the models on the warm setting are much better than that of the warm-cold hybrid setting (the normal setting as shown in Table 2), exhibiting that the existence of cold-start items significantly deteriorate the performance; and 2) the performance gap between CLHE and the strongest baseline in the hybrid setting is obviously much larger than that on the warm setting, implying that our method’s strength in dealing with cold-start items. 3.4.1 Cold-start Items. Another merit of our approach is that the contrastive learning is able to counter the sparsity and noise issue when the input partial bundle is incomplete. To elicit this property, we corrupt the testing dataset to make the input partial bundle sparse and noisy. Specifically, we randomly remove certain portion of items from the input partial bundle to make them sparser. To make the partial bundle more noisy, we randomly sample some items from the whole item set and add them to the bundle. Then we test our model and the model without both levels of contrastive loss, and the performance curves are shown in Figure 3, where the x-axis is the ratio of bundle size after corruption compared with the original bundle, and the ratio=1 corresponds to the original clean data. From this figure, we can derive the conclusion that: 1) with the sparsity and noise degree increasing, both our method and baselines’ performance drops; 2) our method still outperforms baselines even under quite significant sparsity or noise rate, such as removing 50% seed items or adding 50% more noisy items; and 3) the contrastive loss in our model is able to combat the parse and noise bundle issue to some extent. 3.4.2 Sparsity and Noise in Bundle. Data augmentation is the crux to contrastive learning. We search over multiple different data augmentation strategies at both item- and bundle-level contrastive learning, in order to find the best-performing setting. In Table 5, we present the performance of CLHE under various data augmentations at both item- or bundle-level. Overall speaking, data augmentation methods may affect the performance and proper selection is important for good results. 3.4.3 Data Augmentations. Self-attention calculates every pair of instances in a set, i.e., features of an item or items of a bundle, thus it usually suffers from high computational complexity. We record the time used for every training epoch and the time used from the beginning of training till convergence, and the records of our method and two baselines, i.e., MultiDAE and Transformer, are illustrated in Figure 4. The bar chart reveals that on the one hand, our method is computationally heavy since it takes the longest time for each training epoch; on the other hand, our method takes the least training time to reach convergence on three datasets. In conclusion, our method is effective and efficient during training, while the inherent complexity induced by hierarchical self-attention may impose the inference slower. We argue that various self-attention acceleration approaches could be considered in practice, which is out of the scope of this work. 3.4.4 Computational Complexity. We would like to further illustrate some cases to portrait how the hierarchical encoder learn the association of multimodal features and the multiple items’ representations. Specifically, for both item- and bundle-level self-attention modules, we take the last layer’s output representation as each feature’s (item’s) representation, and calculate the cosine similarity score with the whole item (bundle). We cherry pick some example items and bundles, as shown in Figure 5. The results of feature-item similarity exhibit that the three type of features could play distinctive roles in different items, showing the importance of all the three types of features. For the item-bundle similarity results, we can find that items do not equally contribute to their affiliated bundles, thus it is crucial to model the bundle composition patterns. Here we just intuitively illustrate some hints about the bundle representation learning, more sophisticate analysis, such as feature pair or item pair co-effects, is left for future work. 3.4.5 Case Study. We also present the model’s performance change w.r.t. the key hyper-parameters, i.e., the temperature 𝜏 in contrastive loss and the weights 𝛼1, 𝛼2 for the two contrastive losses. The curves in Figure 6 reveal that the model is still sensitive to these hyper-parameters, and proper tuning is required to achieve optimal performance. 3.4.6 Hyper-parameter Analysis. This paper is under CC 4.0 license. available on arxiv

CLHE's Triumph in Multimodal Bundle Construction Experimentation

Authors: (1) Davide Viviano, Department of Economics, Harvard University; (2) Lihua Lei, Graduate School of Business, Stanford University; (3) Guido Imbens, Graduate School of Business and Department of Economics, Stanford University; (4) Brian Karrer, FAIR, Meta; (5) Okke Schrijvers, Meta Central Applied Science; (6) Liang Shi, Meta Central Applied Science. Table of Links Abstract & Introduction Setup (When) should you cluster? Choosing the cluster design Empirical illustration and numerical studies Recommendations for practice References A) Notation B) Endogenous peer effects C) Proofs 5 Empirical illustration and numerical studies In this section we illustrate the properties of the procedure in two empirical applications, one using unique data from the Facebook friendship and messaging graphs, and one using data from an experiment conducted in rural China by Cai et al. [2015]. We provide further evidence supporting our theoretical analysis using simulated networks in Section 5.3. 5.1 Clustering on Facebook graphs We first study the procedure’s properties for two clustering algorithms implemented at scale on social networks owned by Meta: Louvain algorithm [Blondel et al., 2008], Balanced Partitioning [Kabiljo et al., 2017]. We consider two graphs owned by Meta. In each graph, edges are continuous variables. In the first graph, edges capture the strength of the friendship in the Facebook graph, and in the second graph they capture connections based on messaging on Facebook, in both cases the data was aggregated and de-identified. We compute our statistics by setting the edges to be zero if below the 5th, 10th, and 50th percentile. We will refer to these graphs obtained as dense, moderate (mod), and sparse after thresholding (graphs all have a bounded maximum degree). We report the bias and variance, with the variance weighted by a parameter ξn, defined respectively as Louvain and Balanced Partitioning clustering produce a hierarchical clustering structure with a growing number of clusters. We consider three “types” of such algorithms, corresponding to three levels in the clustering structure hierarchy (i.e., different numbers of clusters), defined as Type 1, 2, and 3, respectively. For Louvain clustering, higher-order types denote more clusters, whereas it is the opposite for Balanced Partitioning. For each clustering, we report log(Kn/n), log-ratio of the number of clusters over the population size. Table 2 collects each of these algorithms’ worst-case bias and variance and the number of clusters per individual. Louvain algorithm dominates Balanced Partitioning both in worstcase variance and bias. A larger number of clusters increases the bias but decreases the variance, which we observe throughout all graphs. The bias increases for sparser networks because the denominator |Ni | decreases. Figure 3 reports the objective function for different graphs, Louvain algorithms, and degrees of sparsity. For ξn ≈ 1, the Louvain algorithm with the largest number of clusters (Type 3) dominates the other two algorithms in all but one case. This result provides some interpretable comparisons for the number of clusters. We also observe a trade-off between the number of clusters and the degree of sparsity of the adjacency matrix as ξn decreases. Louvain clustering with the largest number of clusters (Type 1) is optimal for a dense graph with ξn ≫ 1. Such clustering, however, is sub-optimal as the graph becomes more sparse. This result is intuitive: for denser networks, a larger number of clusters may best control the estimator’s bias relative to the bias induced by other clustering algorithms. These comparisons motivate trade-offs in the choice of the graph (and its density) and the clustering algorithm. We recommend practitioners compare clustering by averaging objective functions over different sparsity thresholding. For example, it is possible to compare clustering based on the average objective across different graphs using some priors on the degree of sparsity without affecting our theoretical results. Finally, note that using Theorem 3.6 it is possible to compute the smallest values of the spillover effects that would motivate using a cluster instead of Bernoulli design. From Theorem 3.6, researchers should prefer a cluster over a Bernoulli design if In summary, our results shed light on using clustering algorithms for large-scale implementation on online platforms. These results suggest that Louvain clustering with possibly many clusters performs best in practice. It confirms intuitive arguments in Karrer et al. [2021] who also recommend Louvain clustering based on AA-tests. 5.2 Clustering in the field We consider as an application the problem of informing individuals to increase insurance takeup studied in Cai et al. [2015]. The authors collected network information in approximately 184 villages in rural China from 48 larger regions. We use network data collected by Cai et al. [2015] to study the properties of the proposed method, where we assume that two individuals are connected if at least one of the two indicates the other as a friend. Because we do not require information from end-line outcomes, we construct a network using information from surveyed individuals as well as their friends. The network has in total 7649 nodes, once we also include individuals who are friends of surveyed individuals (but do not necessarily live in their same village or were surveyed in the experiment). Individuals between villages present on average 50% of their connections outside their village. On the other hand, individuals in different regions (with approximately 50 regions) have 99% of their connections within the same region. We use an adjacency matrix a “weak ties” adjacency matrix, where two individuals are connected if either indicates the other has a friend. We study clustering within each region and report results for different clustering algorithms. Clustering within each region is performed as in Algorithm 1, where we first estimate Xˆ (K) via semi-definite programming, use K-means algorithm to retrieve the clusters from Xˆ (K) and iterate to estimate the optimal number of clusters K. We report the frontier with respect to the absolute bias consistently with Algorithm 1. 5.2.1 How many clusters should we choose? Figure 4 presents the main results. The left-hand side panel reports the average number of estimated clusters, estimated by the proposed method divided by the population size in a given cluster. Different choices of ξn justify a different number of clusters. For ξn = 1, the number of clusters is 7%, the total population size, whereas for ξn = 15, the number of clusters is roughly half the population size. To gain further intuition of its practical implication, take ψ¯ = 0.24 approximately equal to the outcomes’ variance in Cai et al. [2015] and ϕ¯ n = 0.27 as in Table 2 in Cai et al. [2015]. A recommended choice of ξn is approximately ψ/¯ ϕ¯2 n = 3.29. This choice puts slightly more weight on the bias than the variance when λ = 1, and therefore can be considered a conservative choice for the number of clusters.[13] Even with such a conservative choice, the suggested number of clusters is large relative to the population size. In this case, the optimal number of clusters is around 15% of the overall population size, approximately 600 clusters with a surveyed sample size of 3600 individuals. This number is consistent (in scale) with the choice in the number of clusters in other applications in development, such as Egger et al. [2022], Alatas et al. [2012], but larger than the number of villages that we have in the application in Cai et al. [2015]. 5.2.2 Comparison with other clustering methods Next, we compare the procedure (denoted as “Causal Clustering”) with the following alternative clustering algorithms: ε-net clustering as in Eckles et al. [2017] with ε = 3 as suggested in Eckles et al. [2017]; spectral clustering with a fixed number of clusters equal to n/3, where n is the population size in a given region (note that spectral clustering do not optimize over the number of clusters); Louvain clustering with default parameters selected by the R-package igraph; clustering based on village identity of each individual (creating one additional cluster for those individuals whose village is missing in the data). We contrast the objective function with alternative algorithms in the right-hand side of Figure 4. The proposed method achieves the smallest objective function across all competitors, providing suggestive evidence that Algorithm 1 (with a semi-definite relaxation) works particularly well in practice. For a smaller number of clusters the method is closer to the Louvain clustering algorithm, while for a larger number of clusters the objective of the algorithm is close to the objective of the spectral algorithm. Interestingly, clustering based on village identity leads to the largest loss function. This is because individuals are connected within and between villages in this application. Finally, in Figure 5 (first three panels), we report the mean-squared error obtained by simulating the model in Cai et al. [2015] (Table 2, Column 4) and vary the variance of the residuals σ 2 ∈ {1/4, 1/2, 1} to emulate settings with high, medium and low signal to noise ratio. The model in Cai et al. [2015] assumes that individual outcomes depend on neighbors’ treatments, consistently with our Assumption 1. Our method uniformly outperforms competitors in terms of mean-squared error, except for the Louvain algorithm when the signal-to-noise ratio is particularly high, but not in the remaining settings. In the last panel, we consider settings where the local interference assumption is violated, and we calibrate our model to the specification in Cai et al. [2015] (Column 4, Table 5), where the individual outcome depends on the neighbors’ outcomes, violating Assumption 1 and introducing global dependence. We fix the residuals’ variance to be σ 2 = 1/4, whereas the results are robust as we increase σ 2 . In this setup, we observe a larger mean-squared error of all methods, with the proposed method achieving the lowest mean-squared error. 5.3 A numerical study Next, we illustrate the properties of the method in numerical studies. We consider three different data generating processes for the network formation: a geometric network formation, Albert Barabasi network and Erdos-Renyi graph. The geometric network takes the form Ai,j = 1{|Xi,1 − Xj,1|/2 +|Xi,2 − Xj,2|/2 ≤ rn} where rn = p 4/2.75n similarly to simulations in Leung [2020]. Here, Xi,1, Xi,2 are drawn independently from a uniform distribution between [−1, 1]. For the Albert-Barabasi network we first draw n/5 edges uniformly according to Erdos-Renyi graph with probabilities p = 10/n, and second, we draw sequentially connections of the new nodes to the existing ones with probability equal to the number of connection of each pre-existing node divided by the overall number of connections. Finally, the Erdos-Renyi graph is generated with probability of connection p = 2/n. We study the properties of the proposed method and of the same competitors discussed in Section 5.2. Figure 6 showcases that the proposed procedure leads to the lowest objective function. This result is consistent with our theoretical findings. Figure 7 showcases that the number of clusters increases in the sample size, varies substantially across different data generating processes, and increases in ξn. [13] This follows from the fact that ξn is proportional to ψ/¯ ϕ¯2 n for clusters whose bias is of order O(ϕ¯ n), and researcher’s goal is to minimize the worst-case mean squared error (λ = 1). This paper is under CC 1.0 license. available on arxiv

Design of Cluster Experiments: Empirical Illustration and Numerical Studies

Learning how to query data from databases is not just for data scientists, it’s an essential skill for developers, too. Fortunately, NoSQL databases are schema-agnostic. You don’t need to be an expert in thousands of combinations of SQL, data types, and databases to master SQL queries. A good SQL agnostic parser will take care of all but impossible complex queries with ease. If you want to build full-stack applications, it’s important to create apps that use data from databases. Databases can store varies amounts of information in any format, such as text, images, or video. These data stores need to ‘talk’ with one another. Here the role of SQL agnostic parsing software becomes crucial, enabling databases to work among multiple systems rather than having to create a single customized solution from scratch that will degrade in effectiveness over time. SQL Agnostic Queries Make It Work As organizations intake and process an incoming flood of data and traffic, intelligent approaches to managing and querying a database need to be utilized. The proliferation of database types via open-source database software has contributed to the complexity. It’s not an Oracle-only world today. But even it were, there are still major syntax differences among even the simplest operations across all the major SQL databases. Even though they all implement the same SQL standard. A SQL agnostic query engine can account for all the differences. Database-agnostic querying is necessary to allow engineers to focus on the data they need instead of the data access mechanisms at work behind the scenes. To understand the power of SQL, it helps to think about how different types of queries function. For instance: Data Definition Language (DDL), Data Manipulation Language(DML), and Transactions Control Languag (TCL). These all work as an abstract version for various kinds of SQL statements that can be executed across databases and data types. Agnostic queries focus on answering the important question of ‘what’, rather than ‘how’ or ‘where’ information is stored. It also helps users retrieve information without being database literate. It makes SQL queries much less complex to write.  Users see a single unified representation of all the data available to them and get answers in an easy-to-use format. This doesn't mean that the data retrieved under these queries is not being managed or maintained properly. Data can be in a relational database, a graph, a foreign primary key value, or a time series.  Changes in the database schema are continuously tracked and recorded as they always have under standard SQL. Developers don’t need to know that ‘behind the scenes’ a good SQL agnostic query engine is tracking: The kind of database that houses the data, such as relational or key-value. The particular database instance such as a shard, or a partition The version of the database being used such as PostgeSQL or Microsoft SQL server. This is the magic and efficiency of using a database access tool that uses SQL agnostic queries. Example: Apache ShardingSphere is an open-source database ecosystem of tools and middleware that makes good use of existing database resources rather than building another new database technology. Apache ShardingSphere Apache ShardingSphere is an open-source ecosystem consisting of a set of distributed database solutions, including 3 independent products, JDBC, Proxy & Sidecar. They all provide functions of data scale out, distributed transaction, and distributed governance functions, applicable in a variety of situations such as Java isomorphism, heterogeneous language, and cloud native. Apache ShardingSphere is an Apache Top-Level project and is one of the most popular open-source big data projects. It was started about 5 years ago and now has 14K+ stars, over 300 contributors, and 5,100 forks in its community. Here is an exmaple of a query using ShardingSphere’s SQLParserEngine access tool: <insert code snippet here> The queries are more accessible for developers or anyone who wishes to extract information from the database since it's in a format that makes sense, rather than being all over the place with no standard for access. Advantages of SQL Agnostic Queries Backing For Unstructured Text The digital world is changing faster than ever before. It's hard to imagine that there could be so much unstructured data in the world, and yet we're barely scratching the surface. The reality is that 80% or more of all enterprise-level information comes from non-document sources, for example, text messages between employees on their phones. This means companies need a new approach if they want access to valuable business intelligence about what matters most - without having an entire research staff dedicated solely towards understanding where and how to access the data as it comes in. Numerous NoSQL data sets can deal with the ordering of unstructured text either as a local element (MarkLogic Server) or a coordinated arrangement of administrations, including Solr or Elasticsearch. A good SQL agnostic tool is like a Swiss Army knife for your data. It can answer the question of what, when and how much by translating unstructured or disparate elements into something useful that will get you closer to an answer than ever before. Managing Complexity Data scientists have persevered in finding ways to solve queries using the power of data for decades. The first step was optimizing databases, which was complicated and time-consuming, done ‘by hand' from memory - but could not handle large multi-table joins without breaking a sweat! Today's sharding solutions can split up huge tables across many databases so they don't get too heavy on any one server farm or database, but it makes queries more complex. NoSQL parsing software excels at hiding that complexity from users who simply want fast, insightful data retrieval. Reducing Cost The NoSQL data sets handle apportioning (sharding) of a single large, unorganized file across servers. This model supports less expensive commodity hardware and software which can result in significant cost savings for organizations. They won’t have to purchase additional server space or storage media with higher prices than is necessary. To Summarize SQL is the de facto language used to access many different databases and data types for all of today’s apps. SQL agnostic queries are now necessary to  write applications for multiple databases and data types. The syntax of SQL agnostic queries is very similar to standard SQL, so it's easy to learn and use. You don't have to switch between languages when you need a new database. It's easier than ever before because there are more tools like ShardingSphere available for making this process simple and efficient. Now, no matter what major changes come up with future versions of your favorite data platform, you'll always be able to keep using it without any issues, thanks to SQL agnostic queries.

SQL Queries: Why You Need SQL-Agnostic Parsing

It is nice to be able to analyze the code for either code review automation or rule enforcement. While is an extremely useful platform for C# code analysis, more often than not, .NET applications come backed by SQL Server. And this means one thing… Roslyn We need Roslyn for SQL! We probably are not getting full-blown SQL dependency tracking system just yet. But apparently, Microsoft does offer a that is very capable of handling most static code analysis on SQL. Let us run through a reasonably common scenario. Developers rely on feature flags for all new functionality. Product Owner explicitly enables features are they become ready. States are kept in the database and managed via simple scripts. Since we release code multiple times a day, unfinished features can ship to production as long as respective flags are safely turned off. Now our task is to ensure that change scripts only create feature flag definitions but never turn them on by default. SqlParser library A bit of theory Parsing language (usually) happens in stages: Lexical analysis (convert character stream to basic building blocks – ) tokens Syntactic analysis (group tokens into constructs according to selected grammar – ) parse tree Semantic analysis (convert parse tree into ) abstract syntax tree Once we’ve got the (AST), we can interact with it and perform certain operations depending on node type and context. Probably the easiest way to traverse such tree would be to implement a . Both and offer stubs to aid implementing this pattern. Abstract Syntax Tree Visitor Roslyn SQL Parser Sample scripts This particular example revolves around two workflows: -- case 1 - definе new feature flag
INSERT INTO dbo.FeatureFlag ( [Key], Value ) VALUES ( @featureFlagId, N'true')
-- case 2 - update existing flag
UPDATE dbo.FeatureFlag SET Value = 'true' WHERE [Key] = @featureFlagId -- enable/disable flag state Let’s imagine we’ve got a directory where developers put new scripts. We need to scan it and figure out whether any scripts attempt to update the in our table: Value using Microsoft.SqlServer.Management.SqlParser.Parser;
......	
	foreach (var file in Directory.EnumerateFiles(@"C:\ChangeScriptsFolder"))
	{
		var result = Parser.Parse(File.ReadAllText(file)); // parse SQL. That's all code we need to get an Abstract Syntax Tree
		var visitor = new FeatureFlagStateVisitor(file); // prep our visitor
		result.Script.Accept(visitor); // run
		if (!visitor.Result.IsSuccess)
		{
			string errorList = visitor.Result.Errors.Aggregate(new StringBuilder(), (sb, error) => sb.AppendLine(error)).ToString();
			throw new Exception($"FeatureFlag state must be disabled on build. Sql text analysis indicates following issues: {errorList}");
		}
	}
....... And the visitor itself can look something like this: public class SqlAnalysisResult
    {
        public bool IsSuccess { get; set; }
        public IList<string> Errors { get; set; }
    }
    public class FeatureFlagStateVisitorContext
    {
        public bool IsFtUpdating { get; set; }
        public bool IsFtInserting { get; set; }
        public string StatementLocation { get; set; }
        public int InsertColumnRef { get; set; }
    }
    public class FeatureFlagStateVisitor: SqlCodeObjectRecursiveVisitor
    {
        public SqlAnalysisResult Result { get; set; }
        private readonly FeatureFlagStateVisitorContext _context;
        private readonly string _fileName;

        public FeatureFlagStateVisitor(string fileName)
        {
            Result = new SqlAnalysisResult
            {
                Errors = new List<string>(),
                IsSuccess = true
            };
            _context = new FeatureFlagStateVisitorContext();
            _fileName = fileName;
        }

        public override void Visit(SqlInsertSpecification codeObject)
        {
            if (codeObject.Target.Sql.CaseInsensitiveContains("FeatureFlag"))
            {
                _context.InsertColumnRef = codeObject.TargetColumns.IndexOf(codeObject.TargetColumns.FirstOrDefault(c => c.ColumnName.Sql.Contains("Value")));
                if (_context.InsertColumnRef >= 0)
                {
                    _context.IsFtInserting = true;
                    _context.StatementLocation = $"L{codeObject.StartLocation.LineNumber}:{codeObject.StartLocation.Offset} - L{codeObject.EndLocation.LineNumber}:{codeObject.EndLocation.Offset}";
                }
            }
            base.Visit(codeObject);
            _context.IsFtInserting = false;
        }

        public override void Visit(SqlRowConstructorExpression codeObject)
        {
            if (_context.IsFtInserting && codeObject.Values[_context.InsertColumnRef].Sql.CaseInsensitiveContains("true"))
            {
                Result.IsSuccess = false;
                Result.Errors.Add($"INSERT {_fileName} - {_context.StatementLocation}");
            }
            base.Visit(codeObject);
        }

        public override void Visit(SqlUpdateSpecification codeObject)
        {
            if (codeObject.Target.Sql.CaseInsensitiveContains("FeatureFlag"))
            {
                _context.IsFtUpdating = true;
                _context.StatementLocation = $"L{codeObject.StartLocation.LineNumber}:{codeObject.StartLocation.Offset} - L{codeObject.EndLocation.LineNumber}:{codeObject.EndLocation.Offset}";
            }
            base.Visit(codeObject);
            _context.IsFtUpdating = false;
        }

        public override void Visit(SqlColumnAssignment codeObject)
        {
            if (_context.IsFtUpdating && codeObject.Column.Sql.CaseInsensitiveContains("Value") && codeObject.Value.Sql.CaseInsensitiveContains("true"))
            {
                Result.IsSuccess = false;
                Result.Errors.Add($"UPDATE {_fileName} - {_context.StatementLocation}");
            }
            base.Visit(codeObject);
        }
    } The idea is pretty simple – we keep track of where we are at the moment and refer back to this context when making the final decision on level. SqlColumnAssignment/SqlRowConstructorExpression

We need Roslyn for SQL!

Parsing SQL queries provides superpowers for monitoring data health. This post describes how to get started on parsing  SQL for data observability. Query history of a data warehouse is a rich source of information to glean how data is used in your organization. Many aspects of data observability can be tracked by analyzing query history. For example, query history analysis can extract: Popular tables and columns Unused tables and columns Column-level lineage Freshness These statistics also help to automate common data engineering tasks like: Backup and Disaster Recovery Triage Data Quality issues Track sensitive data and how they are used. Challenges and Approaches SQL language is an ISO/IEC standard and the latest version is . However, every database implements the standard differently, uses different function names for the same operation, and has extensions to access specific custom features. Therefore, there isn’t one SQL parser for dialects of all popular databases and data warehouses. SQL2016 Regular expressions is a popular approach to extract information from SQL statements. However, regular expressions quickly become too complex to handle common features like WITH, sub-queries, windows clauses, aliases and quotes. is a popular Python package that uses regular expressions to parse SQL. sqlparse An alternate approach is to implement the SQL grammar using parser generators like . There are similar open-source parser generators in other popular languages. There are multiple projects that maintain parsers for popular open-source databases like MySQL and Postgres. For other open-source databases, the grammar can be extracted from the open-source project. For commercial databases, the only option is to reverse engineer the complete grammar. There are SQL parser/optimizer platforms like that help to reduce the effort to implement the SQL dialect of your choice. ANTLR Apache Calcite Open Source Parsers Some popular open-source databases and data warehouses are: MySQL/MariaDB parser is a MySQL parser in Go. Pingcap in phpmyadmin is a validating SQL lexer and parser with a focus on MySQL dialect. SQL Parser Postgres extracts the parser (written in C) from the Postgres project and packages it as a stand-alone library. This library is wrapped in other languages by other projects like: libpg_query : Python pglast : Ruby pg_query : Golang pg_query_go : in Node and in the browser JS psql-parser pg-query-emscripten : Rust pg_query.rs Multiple Engines implements Apache Hive, Presto/Trino and Vertica dialects. queryparser implements BigQuery, Spanner, and Dataflow dialects. zetasql Generic Parsers : Python sqlparse : Rust sqlparser-rs : Python mo-sql-parseing Platforms Parser/Optimizer platforms implement the common SQL language features and allow customization as a first-class feature of the platform. Two popular open-source projects are: is a popular parser/optimizer that is used in popular databases and query engines like , , and . Apache Calcite Apache Hive BlazingSQL many others can parse multiple SQL dialects like MySQL, Postgres and Oracle. The grammar can be modified to support other SQL dialects. JSQLParser Apache Calcite allows customizations at various points of the parsing process. Parser rules can be to support custom syntax. changed Conventions such as quotes vs double quotes, case sensitivity. Add optimizer rules. Apache Calcite also provides visitors for traversing the SQL execution plan. Visitor pattern is an algorithm to traverse a SQL plan. Practical tips to Getting Started There are many abandoned open-source SQL parsers. The first filter is to use a project that will be supported in the future. For popular databases such as Postgres and MySQL/MariaDB, there are parsers available in multiple programming languages. What if there is no parser for your database? Most teams do not create a parser from scratch. A popular option is to use the Postgres parser and then add custom SQL syntax. AWS Redshift, Vertica, and DuckDB are examples. Use a Postgres SQL parser to parse the query history of these databases to parse the majority of the queries. Many queries will fail to parse such as UNLOAD in AWS Redshift. If it is important to also parse the variants, consider modifying the projects to accept the custom grammar OR use a platform like Apache Calcite. Conclusion There is a demand for SQL parsers to build reports on database or data warehouse usage. There are a number of good open-source projects. However, there is a steep learning curve to use these projects and in many cases, a project may not fit your specific requirements. Struggling with parsing query history? Get in touch Also published . on Tokern

14 Open Source SQL Parsers

14 analizadores SQL de código abierto

Yet more code smells? Aren't them enough? We see several symptoms and situations that make us doubt the quality of our development. Let's look at some possible . solutions Most of these smells are just hints of something that might be wrong. They are not rigid rules. This is part V. Part I can be found , Part II , Part III is , Part IV , , , VII, VIII, IX and (for now). here here here here part V VI the last one Let's continue... Code Smell 51 - Double Negatives Not operator is our friend. Not not operator is not our friend. Problems Readability Solutions Name your variables, methods and classes with positive names. Sample Code Wrong ( !work.isNotFinished() ) if Right ( work.isDone() ) if Detection This is a semantic smell. We need to detect it on code reviews. We can tell linters to check for Regular Expressions like or etc as a warning. !not !isNot Tags Readability Conclusion Double negation is a very basic rule we learn as junior developers. There are lots of production systems filled with this smell. We need to trust our test coverage and make safe renames and other refactors. More info What Exactly Is A Name: Rehab [Part II] Remove Double Negative Knot-of-nots: Avoiding negative names for boolean methods Credits Photo by on Daniel Herron Unsplash It’s harder to read code than to write it. Joel Spolsky Code Smell 52 - Fragile Tests Tests are our safety nets. If we don’t trust on their integrity, we will be in great danger. Problems Determinism Confidence loss Wasted time Solutions Test should be in full control. There should be no space for erratic behavior and degrees of freedom. Remove all tests coupling. Coupling: The One and Only Software Designing Problem Examples Fragile, Intermittent, Sporadic or Erratic tests are common in many organizations. Nevertheless, they mine the developers trust. We must avoid them. Sample Code Wrong org.junit.Assert.assertEquals; org.junit.Test; components.set.Set; components.set.Set1L;

public abstract {

    protected abstract < > ();

    @Test
    public final void testAddEmpty() { < > s = .constructor();
        s.add( );
        s.add( );
        assertEquals( , s.toString()); }   
} import static import import import class SetTest Set String constructor Set String this "green" "blue" "{green. blue}" //This is fragile since it dependes on set sort (which is not defined) Right org.junit.Assert.assertEquals; org.junit.Test; components.set.Set; components.set.Set1L;

public abstract {

    protected abstract < > ();

    @Test
    public final void testAddEmpty() { < > s = .constructor();
        s.add( );
        assertEquals( , s.toString());
    }   

    @Test
    public final testEntryAtSingleEntry() { < > s = .createFromArgs( ); x = s.contains( );
        assertEquals( , x);
    } 
} import static import import import class SetTest Set String constructor Set String this "green" "{green}" void Set String this "red" Boolean "red" true Detection Detection can be done with test run statistics. It is very hard to put some test in maintenance since we are removing a safety net. More Info How to Avoid Fragile Unit Tests? Tags Coupling Determinism Conclusion Fragile tests show system coupling and not deterministic or erratic behavior. Developers spend lots of time and effort fighting against this false positives. The amateur software engineer is always in search of magic. Grady Booch Code Smell 53 — Explicit Iteration We learned loops back in school. But enumerators and iterators are the next generation. Photo by on Elena Mozhvilo Unsplash Problems Encapsulation. Declarativeness. Solutions Favor or high order iterators foreach() You will be able to use yield(), caches, proxies, lazy loading and much more when you hide your implementation details. Sample Code Wrong (i = ; i < colors.count(), i++) {
  print(colors[i]);
} for 0 Right foreach (color colors) {
  print(color);
} colors.foreach( print(color)); of //Closures and arrow functions => color Detection Linters can find this smell using regex. There might be false positives. See exceptions below. Exceptions If the problem domain needs the elements to be bijected to natural numbers like indices, the first solution is adequate. Remember all time to find real world analogies. The One and Only Software Design Principle Tags Declarative Conclusion This kind of smell do not ring the bell to many developers because they think this is a subtlety. Clean code is full of this few declarative things that can make a difference. More info What is (wrong with) software? If you get tired of writing for loops, take a break and continue later. David Walker Code Smell 54 — Anchor Boats Code is there. Just in case. We might need it soon. Photo by on Kris Mikael Krister Unsplash Problems Complexity Coupling Solutions Remove dead code. Leave covered and real tested code. Sample Code Wrong { { } { }  
} <? final class DatabaseQueryOptimizer public function selectWithCriteria ($tableName, $criteria) //Make some optimizations manipulating criterias private : function sqlParserOptimization (SQLSentence $sqlSentence) SQLSentence //Parse the SQL converting it to an string and then working with their nodes as strings and lots of regex //This was a very costly operation overcoming real SQL benefits. //But since we made too much work we decide to keep the code. Right { { } 
} <? final class DatabaseQueryOptimizer public function selectWithCriteria ($tableName, $criteria) //Make some optimizations manipulating criterias Detection Using some variants we can remove the dead code and see it test fails. mutation testing We need to have good coverage to rely on this solution. Tags YAGNI Conclusion Dead code is always a problem. We can use modern development techniques like TDD to ensure all code is alive. We all love T.D.D. We know its benefits, we have read a thousand tutorials on how to build a system using this… More info Exception not Found Wikipedia Refactoring.guru It is very hard to predict, especially the future. Niels Bohr Code Smell 55 — Object Orgy If you see your objects as data holders you will violate their encapsulation, but you shouldn’t, as in real life, you should always ask for consent. Picture by Nicolas Poussin Problems Information Hiding Violation Encapsulation Violation Coupling Solutions Couple to interfaces and behavior, never data. Sample Code Wrong { $x; $y;
} { { sqrt((($destination->x - $origin->x) ^ ) + (($destination->y - $origin->y) ^ ));
    }
} <? final class Point public public final class DistanceCalculator function distanceBetween (Point $origin, Point $destination) return 2 2 Right { $rho; $theta; { ->rho * cos( ->theta);
    } { ->rho * sin( ->theta);
    }
} { { sqrt((($destination->x() - $origin->x() ^ ) + (($destination->y() - $origin->y()) ^ )));
    }

} <? final class Point private private public function x () return $this $this public function y () return $this $this final class DistanceCalculator function distanceBetween (Point $origin, Point $destination) return 2 2 Detection You can set your linters to warn you for public attributes, setters and getters usage and discourage them. Tags Coupling Conclusion If your classes are polluted with setters, getters and public methods you will certainly have ways to couple to their accidental implementation. Also Known as Inappropriate intimacy More info Object Orgy A data structure is just a stupid programming language. Bill Gosper We are done for now. But we are pretty sure we will come across even more smells very soon! I keep getting more suggestions on , so they won't be the last! twitter

How to Find The Stinky Parts of Your Code [Part XI]

Today, we are going to discuss a proposed by researchers from four institutions one of which is ByteDance AI Lab (known for their TikTok App). They give us a new method termed (not confuse with Sparse R-CNN that works with sparse convolutions on 3D computer vision tasks such ) that achieve near state-of-the-art performance in object detection and uses completely sparse and learnable bounding boxes generation. method Sparse R-CNN that Let’s start with a short overview of existing detecting methods. Dense method One of the widely used pipelines is the , which directly predicts the label and location of anchor boxes densely covering spatial positions, scales, and aspect ratios in a single-shot way. For example or . one-stage detector SSD YOLO Let’s consider the YOLO algorithm. Ultimately, it aims to predict a class of an object on the image and the bounding box specifying object location. Each bounding box can be described using four descriptors: center of a bounding box ( ) bx, by width ( ) bw height ( ) bh value is corresponding to a class of an object (such as: car, traffic lights, etc.). c In addition, we have to predict the value, which is the probability that there is an object in the bounding box. pc It is a because it is not searching for interesting regions in the given image that could potentially contain an object. Instead, YOLO is splitting the image into cells, using 19×19 grid. But in general one-stage detector could produce × cells, one per pixel. Each cell is responsible for predicting bounding boxes (for this example is chosen as 5). Therefore, we arrive at a large number of × × bounding boxes for one image. dense method W H k k W H k For each cell in the grid and each bbox mentioned above values are produced ( source ) Dense-To-Sparse Method There are two-stage detectors that piggy-backs on dense proposals generated using RPN like the paper proposed. These detectors have dominated modern object detection for years. Faster R-CNN Using RPN it “ from dense region candidates, and then refines the location of each proposal” and predicts its specific category. obtains a sparse set of foreground proposal boxes Two-stage detector architecture ( source ) Proposals are obtained in a similar way as in one-stage detectors, but instead of predicting the class of object directly, it predicts objectness probability. After that, the second stage predicts classes filtered by objectness and overlap score bounding boxes. Sparse Method Authors of this paper categorize a paradigm of their new Sparse R-CNN as the extension of the existing object detector paradigm which includes from thoroughly dense to dense-to-sparse with a new step which leads to thoroughly sparse. Model architectures comparison ( source ) In the reviewed paper using RPN is avoided and replaced with a small set of proposal boxes (e.g. 100 per image). These boxes are obtained using the learnable part and part of the network. The formal predicts values per proposal and the latter predicts latent representation vector of length 256 of each bbox contents. Learned proposal boxes are used as a reasonable statistic to perform refining steps afterward and learned proposal features used to introduce attention mechanism. This mechanism is very similar to one that is used in the paper. proposal boxes proposal features 4 (x,y,h,w) DETR These manipulations are performed inside the dynamic instance interactive head that we will cover in the following section. Proposed Model Features As the name of the paper implies, this model is end-to-end. The architecture is elegant. It consists of FPN based backbone that acquires features from images, mentioned above learnable and and a dynamic instance interactive head which is the main contribution to neural nets architecture of this very paper. proposal boxes proposal features, Dynamic instance interactive head Given proposal boxes, Sparse R-CNN first utilizes the RoIAlign operation to extract features from the backbone for each region defined with proposal bounding boxes. Each RoI feature “ ”. N is fed into its own exclusive head for object location and classification, where each head is conditioned on specific learnable proposal feature Dynamic module ( source ) Proposal features are used as weights for convolutions, on the image above they are mentioned as “Params.” The RoI feature is processed by this generated convolution to obtain the final feature. In this way, those “ ”. The self-attention module is embedded into the dynamic head to reason about the relations between objects and affects predictions by this convolution. bins with most foreground information make effect on final object location and classification Main Result The authors provide several comparison tables that show the performance of a new method. Sparse R-CNN is compared to RetinaNet, Faster R-CNN, and DETR in two variations with ResNet50 and ResNet100. Model performance ( source ) Here we can see that Sparse R-CNN outperforms RetinaNet and Faster R-CNN in both R50 and R100, but it performs quite similarly to DETR based architectures. According to the authors, the DETR model is in practice a dense-to-sparse model because it utilizes a sparse set of object “ ”. Hence the novelty of the article is arise comparing to DETR. queries, to interact with global(dense) image feature Qualitative analysis ( ) source On that image, you can see the qualitative result of model inference on the COCO Dataset. In the first column learned proposal boxes are shown, they are predicted for any new image. In the next columns, you can see final bboxes that were refined from proposals. They differ depending on the stage in the iterative learning process. Show me the code! In conclusion, I would like to say that now in 2020 we saw a lot of papers that apply transformers to images. Transformers have proved their worth in fields of NLP and now they gradually enter the scene of image processing. This paper shows us that using transformers it is possible to create fast one-stage detectors that perform comparable in terms of quality to the best, for now, two-stage ones. All details on implementation, you can find in the author’s code that based on FAIR’s DETR and detectron2 codebases: https://github.com/PeizeSun/SparseR-CNN References [1] Faster R-CNN: Towards Real-Time Object Detection with Region Proposal Networks https://arxiv.org/abs/1506.01497 [2] YOLO Algorithm and YOLO Object Detection: An Introduction https://appsilon.com/object-detection-yolo-algorithm/ [3] Sparse R-CNN: End-to-End Object Detection with Learnable Proposals https://arxiv.org/abs/2011.12450 Also published here

SIGPARSER

sigparser.com

-- employees

Since n.d.

COMPANY RANKING

SIGPARSER

EVERGREEN INDEX #9369

Representing this company? Click here to claim and customize this page!

SigParser WIKI

Apply to get your Tech Company News Page live on HackerNoon.com today!

SIGPARSER

sigparser.com

-- employees

Since n.d.

COMPANY RANKING

SIGPARSER

EVERGREEN INDEX #9369

RELATED COMPANIESRANK

STORIES ABOUT SIGPARSER

Representing this company? Click here to claim and customize this page!

SigParser WIKI

Apply to get your Tech Company News Page live on HackerNoon.com today!