{"action":"created","issue":{"url":"https://api.github.com/repos/apache/spark/issues/17359","repository_url":"https://api.github.com/repos/apache/spark","labels_url":"https://api.github.com/repos/apache/spark/issues/17359/labels{/name}","comments_url":"https://api.github.com/repos/apache/spark/issues/17359/comments","events_url":"https://api.github.com/repos/apache/spark/issues/17359/events","html_url":"https://github.com/apache/spark/pull/17359","id":215346566,"number":17359,"title":"[SPARK-20028][SQL] Add aggreagate expression nGrams","user":{"login":"gczsjdy","id":7685352,"avatar_url":"https://avatars1.githubusercontent.com/u/7685352?v=4","gravatar_id":"","url":"https://api.github.com/users/gczsjdy","html_url":"https://github.com/gczsjdy","followers_url":"https://api.github.com/users/gczsjdy/followers","following_url":"https://api.github.com/users/gczsjdy/following{/other_user}","gists_url":"https://api.github.com/users/gczsjdy/gists{/gist_id}","starred_url":"https://api.github.com/users/gczsjdy/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/gczsjdy/subscriptions","organizations_url":"https://api.github.com/users/gczsjdy/orgs","repos_url":"https://api.github.com/users/gczsjdy/repos","events_url":"https://api.github.com/users/gczsjdy/events{/privacy}","received_events_url":"https://api.github.com/users/gczsjdy/received_events","type":"User","site_admin":false},"labels":[],"state":"closed","locked":false,"assignee":null,"assignees":[],"milestone":null,"comments":13,"created_at":"2017-03-20T07:35:49Z","updated_at":"2018-04-13T23:16:58Z","closed_at":"2017-10-10T07:25:54Z","author_association":"CONTRIBUTOR","pull_request":{"url":"https://api.github.com/repos/apache/spark/pulls/17359","html_url":"https://github.com/apache/spark/pull/17359","diff_url":"https://github.com/apache/spark/pull/17359.diff","patch_url":"https://github.com/apache/spark/pull/17359.patch"},"body":"## What changes were proposed in this pull request?\r\n\r\nThis is the implementation of `ngrams` aggregate expression which is also implemented by Hive. It takes use of n-gram concept in natural language processing to understand texts.\r\n\r\nCurrently, Spark doesn't support using Hive UDAF GenericUDAFnGrams, which is actually a feature missing.\r\n\r\nAn n-gram is a contiguous subsequence of n item(s) drawn from a given sequence. This expression finds the k most frequent n-grams from one or more sequences. \r\n\r\nThis expression has the pattern of : ngrams(children: Array[Array[String]](or Array[String]), n: Int, k: Int, accuracy: Int), it can be used in conjuction with `sentences` to split the column of String to Array. Among the parameters: \r\nChildren indicates the 'given sequence' we collect n-grams from;\r\nN indicates n-gram's element number, size 1 is referred to as a \"unigram\", size 2 is a \"bigram\", size 3 is a \"trigram\"... \r\nK indicates top k;\r\nAccuracy is related to the memory used for frequency estimation, more memory will give more accurate frequency counts.\r\n\r\nA simple example: \r\n`SELECT ngrams(array(\"abc\", \"abc\", \"bcd\", \"abc\", \"bcd\"), 2, 4);` will get\r\n`[{[\"abc\",\"bcd\"]:2.0}, \r\n{[\"abc\",\"abc\"]:1.0}, \r\n{[\"bcd\",\"abc\"]:1.0}]`. Because there are four 2-grams for the input which are `[\"abc\", \"abc\"], [\"abc\", \"bcd\"], [\"bcd\", \"abc\"], [\"abc\", \"bcd\"]`, and `[\"abc\", \"bcd\"]` occurs 2 times, the other two 2-grams occurs 1 time each, while `[\"abc\",\"abc\"]` is alphabetically before `[\"bcd\",\"abc\"]`, so the answer is like that.\r\n\r\nAn interesting example for presidential candidates' speech analysis:\r\nI watched the US presidential election a few months ago, and wanted to get candidates' viewpoints and the phrases they'd like to use. How can I easily analyze the text without too much programming?\r\n\r\nI did a simple test using this expression:\r\n* Download the final debate transcript \r\n* Seperate different candidates' words to different files, translate punctuations to space\r\n* load 2 files each as a single string to different tables in Spark SQL\r\n* Execute `SELECT ngrams(sentences(lower(text)), 4, 40) from debate_trump(/clinton)`\r\n\r\nIf I ignored the word sequences like (we, are, going, to) which is said 27 times by Trump and 18 times by Clinton, the phrases they said a lot during the debate will be:\r\n\r\nFor Trump, (millions, of, people, that) for 4 times, (iran, is, taking, over) for 3 times, (putin, has, outsmarted, her) for 3 times, (she, wants, open, borders) for 3 times, (a, country, of, laws) for 2 times\r\n\r\nFor Clinton, (30, years, of, experience) for 2 times, (clear, to, the, russians) for 2 times, (grow, the, economy, to) for 2 times, (have, met, with, women) for 2 times.\r\n\r\nAnother example for movie reviews analysis:\r\nIf you have a column with movie or product reviews from users expressed as natural language strings. You want to find the top 10 most frequent word pairs. Let's do a simple test first, executing `SELECT ngrams(sentences(\"I hated this movie. I hated watching it and this movie made me unhappy.\"), 2, 4)` will get `[{[\"I\", \"hated\"]:2.0}, {[\"this\", \"movie\"]:2},{[\"hated\", \"this\"]:1},{ngram: [\"hated\", \"watching\"]:1}]`. And that result tells you people have a negative evaluation on that movie.\r\n\r\n## How was this patch tested?\r\n\r\nUnit tests are in `NGramsQuerySuite` \r\n"},"comment":{"url":"https://api.github.com/repos/apache/spark/issues/comments/381282297","html_url":"https://github.com/apache/spark/pull/17359#issuecomment-381282297","issue_url":"https://api.github.com/repos/apache/spark/issues/17359","id":381282297,"user":{"login":"sijunhe","id":11987277,"avatar_url":"https://avatars1.githubusercontent.com/u/11987277?v=4","gravatar_id":"","url":"https://api.github.com/users/sijunhe","html_url":"https://github.com/sijunhe","followers_url":"https://api.github.com/users/sijunhe/followers","following_url":"https://api.github.com/users/sijunhe/following{/other_user}","gists_url":"https://api.github.com/users/sijunhe/gists{/gist_id}","starred_url":"https://api.github.com/users/sijunhe/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/sijunhe/subscriptions","organizations_url":"https://api.github.com/users/sijunhe/orgs","repos_url":"https://api.github.com/users/sijunhe/repos","events_url":"https://api.github.com/users/sijunhe/events{/privacy}","received_events_url":"https://api.github.com/users/sijunhe/received_events","type":"User","site_admin":false},"created_at":"2018-04-13T23:16:58Z","updated_at":"2018-04-13T23:16:58Z","author_association":"NONE","body":"Would love to see this feature in Spark SQL."}}
{"actor":{"display_login":"sijunhe"}}