LDAInference Example - Teradata Vantage

Machine Learning Engine Analytic Function Reference

Product
Teradata Vantage
Release Number
8.00
1.0
Published
May 2019
Language
English (United States)
Last Update
2019-11-22
dita:mapPath
blj1506016597986.ditamap
dita:ditavalPath
blj1506016597986.ditaval
dita:id
B700-4003
lifecycle
previous
Product Category
Teradata Vantageā„¢

Input

  • InputTable: complaints_testtoken, a tokenized, filtered, input file created by applying the TextParser function to complaints_test, a log of vehicle complaints
  • ModelTable: ldamodel, output by LDA Example
complaints_test
doc_id text_data
1 ELECTRICAL CONTROL MODULE IS SHORTENING OUT, CAUSING THE VEHICLE TO STALL. ENGINE WILL BECOME TOTALLY INOPERATIVE. CONSUMER HAD TO CHANGE ALTERNATOR/ BATTERY AND STARTER, AND MODULE REPLACED 4 TIMES, BUT DEFECT STILL OCCURRING CANNOT DETERMINE WHAT IS CAUSING THE PROBLEM.
2 ABS BRAKES FAIL TO OPERATE PROPERLY, AND AIR BAGS FAILED TO DEPLOY DURING A CRASH AT APPROX. 28 MPH IMPACT. MANUFACTURER NOTIFIED.
3 WHILE DRIVING AT 60 MPH GAS PEDAL GOT STUCK DUE TO THE RUBBER THAT IS AROUND THE GAS PEDAL.
4 THERE IS A KNOCKING NOISE COMING FROM THE CATALYITC CONVERTER ,AND THE VEHICLE IS STALLING. ALSO, HAS PROBLEM WITH THE STEERING.
5 CONSUMER WAS MAKING A TURN ,DRIVING AT APPROX 5- 10 MPH WHEN CONSUMER HIT ANOTHER VEHICLE. UPON IMPACT, DUAL AIRBAGS DID NOT DEPLOY . ALL DAMAGE WAS DONE FROM ENGINE TO TRANSMISSION,TO THE FRONT OF VEHICLE, AND THE VEHICLE CONSIDERED A TOTAL LOSS.
6 WHEEL BEARING AND HUBS CRACKED, CAUSING THE METAL TO GRIND WHEN MAKING A RIGHT TURN. ALSO WHEN APPLYING THE BRAKES, PEDAL GOES TO THE FLOOR, CAUSE UNKNOWN. WAS ADVISED BY MIDAS NOT TO DRIVE VEHICLE- WHEELE COULD COME OFF.
7 DRIVING ABOUT 5-10 MPH, THE VEHICLE HAD A LOW FRONTAL IMPACT IN WHICH THE OTHER VEHICLE HAD NO DAMAGES. UPON IMPACT, DRIVER'S AND THE PASSENGER'S AIR BAGS DID NOT DEPLOY, RESULTING IN INJURIES. PLEASE PROVIDE FURTHER INFORMATION AND VIN#.
8 THE AIR BAG WARNING LIGHT HAS COME ON. INDICATING AIRBAGS ARE INOPERATIVE.THEY WERE FIXED ONE AT THE TIME, BUT PROBLEM HAS REOCCURRED.
9 CONSUMER WAS DRIVING WEST WHEN THE OTHER CAR WAS GOING EAST. THE OTHER CAR TURNED IN FRONT OF CONSUMER'S VEHICLE, CONSUMER HIT OTHER VEHICLE AND STARTED TO SPIN AROUND ,COULDN'T STOP, RESULTING IN A CRASH. UPON IMPACT, AIRBAGS DIDN'T DEPLOY.
10 WHILE DRIVING ABOUT 65 MPH AND THE TRANSMISISON MADE A STRANGE NOISE, AND THE LEFT FRONT AXLE LOCKED UP. THE DEALER HAS REPAIRED THE VEHICLE.

This example uses this stop words file, stopwords.txt:

a
an
in
is
to
into
was
the
and
this
with
they
but
will

To create a tokenized, filtered, input file for the LDAInference function, apply the TextParser function to complaints_test, using the file stopwords.txt:

CREATE MULTISET TABLE complaints_testtoken AS (
  SELECT * FROM TextParser (
    ON complaints_test
    TextColumn ('text_data')
    ToLowerCase ('true')
    Stemming ('false')
    ListPositions ('true')
    StopWords ('stopwords.txt')
    RemoveStopWords ('true')
    Accumulate ('doc_id') 
  ) AS dt
) WITH DATA;

SQL Call

SELECT * FROM LDAInference (
  ON complaints_testtoken AS InputTable
  ON ldamodel AS ModelTable
  OUT TABLE OutputTable (ldaout2)
  USING
  DocIDColumn ('doc_id')
  WordColumn ('token')
  OutputTopicNum (5)
  OutputTopicWordNum (5)
) AS dt;

This query returns the following table:

SELECT * FROM complaints_testtoken ORDER BY doc_id;
complaints_testtoken
doc_id token frequency position
1 electrical 1 0
1 control 1 1
1 module 2 2,25
1 shortening 1 4
1 out 1 5
1 causing 2 6,37
1 vehicle 1 8
1 stall 1 10
1 engine 1 11
1 become 1 13
1 totally 1 14
... ... ... ...

Output

message
There are 10 valid documents with 153 recognized words in the input, the perplexity is 145.918488
Outputtable is created successfully.

This query returns the following table:

SELECT * FROM ldaout2 ORDER BY docid, topicid;
ldaout2
docid topicid topicweight topicwords
1 0 0.00421317772559819 wipers,would,switch,when,on
1 1 0.982025483899112 vehicle,causing,consumer,replaced,which
1 2 0.00449350162478127 vehicle,manufacturer,would,transmission,when
1 3 0.00431128170088637 did,not,deploy,hit,vehicle
1 4 0.0049565550496219 vehicle,side,car,engine,while
2 0 0.237979551143677 wipers,would,switch,when,on
2 1 0.0131031974444299 vehicle,causing,consumer,replaced,which
2 2 0.0980050092051074 vehicle,manufacturer,would,transmission,when
2 3 0.6322490967996 did,not,deploy,hit,vehicle
2 4 0.0186631454071852 vehicle,side,car,engine,while
... ... ... ...