MichaelB.
7/29/2018 - 6:42 PM

Use Text Analysis in queries

SNIPPETS shows context of each query term match. It will show the key term you're looking for, it will additionally show some text before and after it. Gives you a SNIPPET of the source text with the key term identified. For when you just want to show parts of the text before and after the key term. HIGHLIGHTED returns full text content with query terms tagged. Highlights the term within the complete body of text / document. PLAINTEXT allows you to see the original content. Returns text content for a column. Replaces undocumented (but widely known) $SYS_SHADOW column. Works for binary content as well (PDF, Word, etc.) Lets you see the contents within the full-text index, represented as just the text itself (particular tokens of words). LANGUAGE returns the language for a column. MIMETYPE returns the document format for a column.

sql
content_copyfile_download
-- Example of SNIPPETS
SELECT FILE_NAME, SNIPPETS(CONTENT) FROM PRODUCT_REVIEWS
    WHERE CONTAINS(CONTENT, 'meal OR menu OR entree OR appetizer OR food', LINGUISTIC);


-- Example of HIGHLIGHTED
SELECT FILE_NAME, HIGHLIGHTED(CONTENT) FROM PRODUCT_REVIEWS
    WHERE CONTAINS(CONTENT, 'meal OR menu OR entree OR appetizer OR food', LINGUISTIC);
    
    
-- Example of PLAINTEXT
SELECT FILE_NAME, PLAINTEXT(CONTENT) FROM PRODUCT_REVIEWS;


-- Example of LANGUAGE
SELECT FILE_NAME, LANGUAGE(CONTENT) FROM PRODUCT_REVIEWS;


-- Example of MIMETYPE
SELECT FILE_NAME, MIMETYPE(CONTENT) FROM PRODUCT_REVIEWS;


-- Query from $TA Table
SELECT * FROM "$TA_INDEX_NAME"
ORDER BY "Id", "TA_COUNTER";


-- Show most frequently occurring normalized words 
SELECT TA_NORMALIZED WORD,
TA_TYPE "PART-OF-SPEECH",
COUNT(*) FREQUENCY
FROM
"$TA_INDEX_NAME"
GROUP BY TA_NORMALIZED,
TA_TYPE
ORDER BY COUNT(*) DESC;


-- Show most frequently occurring normalized words, based on stem of verb or token
SELECT  WORD, 
		TA_TYPE "PART-OF-SPEECH", 
		COUNT(*) FREQUENCY
FROM 
	(SELECT LOWER(CASE
					WHEN TA_STEM IS NOT NULL
					THEN TA_STEM ELSE TA_TOKEN
					END) WORD, 
			TA_TYPE
	FROM "$TA_INDEX_STREAMS_PUBLIC_TWEETS")
GROUP BY WORD, TA_TYPE
ORDER BY COUNT(*) DESC;


-- Count entities and show their categories filtered, including all of organization subtypes
SELECT TA_TOKEN, TA_TYPE,
COUNT(*) MENTIONS FROM
"$TA_INDEX_STREAMS_PUBLIC_TWEETS"
WHERE TA_TYPE =
'PRODUCT' OR TA_TYPE =
'PROP_MISC' OR TA_TYPE
LIKE 'ORGANIZATION%'
GROUP BY TA_TOKEN,
TA_TYPE
ORDER BY COUNT(*) DESC,
TA_TOKEN;